Fine Grain Auditing example

This is the simplest example of FGA package.
Lets assume you want to audit who selects ID column on a table REDDTA.T1.

exec dbms_fga.add_policy (-
object_schema=>'REDDTA',-
object_name=>'T1',-
policy_name=>'T1_REDDTA',-
audit_column => 'ID',-
statement_types => 'SELECT'-
);

As a result you get something like this:

1* SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail

POLICY_NAME OBJECT_NAME STATEME OS_USER DB_USER
----------- ----------- ------- -------------------- --------------------
T1_REDDTA T1 SELECT oracle REDDTA



DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);

What's worth to know:
audit_column - The columns to be checked for access. These can include hidden columns. The default, NULL, causes audit if any column is accessed or affected.
statement_types - The SQL statement types to which this policy is applicable: INSERT, UPDATE, DELETE, or SELECT only.

Procedure to move outline between instances or schemas

This procedure can be used to move outline between schemas and/ora databases.


CREATE OR REPLACE procedure SYSTEM.outline_get (source_inst in varchar2, outline_name in varchar2, source_owner varchar2, dest_owner varchar2)
is
TYPE typoutput_1 IS REF CURSOR;
replace_user varchar2(64);
output_1 typoutput_1;
sql_to_tune_orig long;
sql_to_tune long;
sql_to_run varchar2(4000);
outline_creator varchar2(4000);
outline_exists number;
outline_prv_exists number;
cursor replace_user_table is
select user_table_name from system.ol$hints where user_table_name is not null;
user_tbl_name varchar2(64);
user_tbl_name_fixed varchar2(64);

begin
/* checking whether temporary private outline exists, drop if exists */
    select count(*) into outline_prv_exists from system.ol$ where ol_name = 'PRV1_TMP';
        if outline_prv_exists > 0 then
            dbms_output.put_line ('Dropping private outline PRV1_TMP');
            execute immediate 'drop private outline PRV1_TMP';
        end if;

/* get sql statement the outline is created for and modify each schema occurance */
sql_to_run :=
    'select sql_text from dba_outlines@'||SOURCE_INST||' where name='''||outline_name||'''';
open output_1 for sql_to_run;
fetch output_1 into sql_to_tune_orig;
sql_to_tune := REPLACE(sql_to_Tune_orig,upper(SOURCE_OWNER),upper(DEST_OWNER));

/* Create temporary private outline for query */
execute immediate ('create private outline PRV1_TMP on '||sql_to_tune);

/* Replace rows from ol$hint from source */
execute immediate ('delete from system.ol$hints where ol_name = ''PRV1_TMP''');
COMMIT;
sql_to_run := 'insert into system.ol$hints select * from outln.ol$hints@'||SOURCE_INST||' where ol_name = '''||outline_name||'''';
execute immediate (sql_to_run);
COMMIT;
update system.ol$hints set ol_name = 'PRV1_TMP';
COMMIT;

/* Change USER_TABLE_NAME for each row */
open replace_user_table;
loop
fetch replace_user_table into user_tbl_name;
user_tbl_name_fixed := replace(user_tbl_name,upper(SOURCE_OWNER),upper(DEST_OWNER));
update system.ol$hints set user_table_name = user_tbl_name_fixed where user_table_name = user_tbl_name;
commit;
exit when replace_user_table%NOTFOUND;
end loop;

/* Refresh private outline after changes */
dbms_outln_edit.refresh_private_outline('PRV1_TMP');

/* Check if outline exists, skip is exists, create if no */
select count(*) into outline_exists from outln.ol$ where ol_name = ''||outline_name||'';
 if outline_exists > 0 then
 dbms_output.put_line ('PUBLIC WITH THIS NAME EXISTS!!!!!');
    else
    execute immediate ('create outline '||outline_name||' from private PRV1_TMP');
    execute immediate ('drop private outline PRV1_TMP');
 end if;
end;
/

How to find value used in bind variables

If sql statement used bind variables is still in shared pool, and you have Oracle 10g, you can find what values variables have in the following way:

select sql_id, name, position,value_string from v$sql_bind_capture where sql_id='';

Datapump EXPDP - ORA-39127 SYS.DBMS_AW_EXP.schema_info_exp error

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp('REDCTL',1,1,'09.02.00.00.00',newblock)
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 473
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp('REDDTA',1,1,'09.02.00.00.00',newblock)
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 505
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
Master table "SYSTEM"."EXPDP_METADATA" successfully loaded/unloaded

SOLUTION:

SQL> DELETE FROM sys.exppkgact$ WHERE package='DBMS_AW_EXP';

2 rows deleted.

SQL> commit;

Commit complete.

Cleanup orphaned datapump jobs

Assume you have some old datapump job, not running for ages:

SQL> select owner_name, job_name,state from dba_datapump_jobs;

OWNER_NAME JOB_NAME STATE
--------------------------------------------------------------------------
SYSTEM TAB_TOREDDTA EXECUTING
DATAPUMP SYS_EXPORT_TABLE_01 NOT RUNNING


You can try attach to this job to use kill_job like this:

expdp datapump/pass@schema attach=SYS_EXPORT_TABLE_01

But in this step you may have a lot of different errors that caused you are not able to attach. How to get rid of this job?
You need to delete master table/ First find it:

SQL> select owner,object_name,status,object_id from dba_objects where object_name = 'SYS_EXPORT_TABLE_01'

OWNER OBJECT_NAME STATUS OBJECT_ID
-------------------- ------------------------------ ------- ----------
DATAPUMP SYS_EXPORT_TABLE_01 VALID 88842

Then drop it:

drop table datapump.SYS_EXPORT_TABLE_01;

And check whether it helps:

SQL> select owner_name, job_name,state from dba_datapump_jobs;

OWNER_NAME JOB_NAME STATE
------------------------------ -----------------------------------------
SYSTEM TAB_TOREDDTA EXECUTING

Identify FULL SCANS in Oracle 10.2.0 database

When you want to see what objects are the most often full scanned, use this:

select object_name,count(*) runs from dba_hist_sql_plan plansql,  dba_hist_sqlstat sqlstat
where operation='TABLE ACCESS' and options='FULL' and object_owner='<schema>'
and plansql.sql_id=sqlstat.sql_id
group by object_name
order by runs desc
/

Then, you can try to identify the most often issued sql statement:

select distinct sqlstat.sql_id, count(*) FOUND_IN_AWR,sqlarea.sql_text from dba_hist_sql_plan plansql,  dba_hist_sqlstat sqlstat, v$sqlarea sqlarea
where operation='TABLE ACCESS' and options='FULL' and object_name ='<found in part1>' and object_owner='<schema>'
and plansql.sql_id=sqlstat.sql_id
and plansql.sql_id = sqlarea.sql_id
group by sqlstat.sql_id,sqlarea.sql_text
order by FOUND_IN_AWR desc
/

Create outline knowing needed hint

Let's assume you have a query with suffer performance problems. You worked out what hint may help, but you cannot apply hint to query as it is genereted by application. In case of simple hints, you can do this updating ol$hint table (in private outline). However sometimes it is not simple to know what updates needs to be done. This is simple way of doing this:

1. Identify query

2. CREATE OR REPLACE OUTLINE ORIGINALSQL ON

/

3. CREATE OR REPLACE OUTLINE HINTSQL ON

/

4. UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL')
/

5. commit
/

6. DROP OUTLINE HINTSQL
/

7. alter outline ORIGINALSQL rename to
/

DataPump: UDE-00019: 'tables' parameter list is too long.

This is due to bug in Oracle <11. When you are using parameter TABLES=, it cannot be longer than 4000 characters. To ommit this you can create table containing list of all the tables you want to export :

CREATE TABLE expdp_tab (owner VARCHAR2(30), object_name VARCHAR2(128), object_type VARCHAR2(19));

INSERT INTO expdp_tab VALUES ('schema','table_name','TABLE');
....
commit;

and then replace TABLES=(...) in parfile with this:
INCLUDE=TABLE:"IN (SELECT OBJECT_NAME FROM EXPDP_TAB)"

Keep in mind one important thing. When you are running direct impdp from source database to local using NETWORK_LINK parameter, table with list of tables needs to be created on source database, if you create this on local, you will get error like this:
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPM$MCP", line 2614

Inbound connection timed out (ORA-3136)

After upgrade database to 10.2.x you can observe such entries in alertlog:

WARNING: inbound connection timed out (ORA-3136)

It is caused by the fact that in 10.2.x default value of parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds.

To fix it:

1. On server side in sqlnet.ora add line:

SQLNET.INBOUND_CONNECT_TIMEOUT=120

2. On server side in listener.ora add line:

INBOUND_CONNECT_TIMEOUT_<listener_name> = 110




How to clean datapump imp/exp

1. Check name and owner of running datapump job:

  1* select owner_name, job_name,state from dba_datapump_jobs

OWNER_NAME                     JOB_NAME                       STATE
------------------------------ ------------------------------ ------------------------------
DATAPUMP                       SYS_IMPORT_FULL_01             EXECUTING
DATAPUMP                       SYS_EXPORT_TABLE_01            NOT RUNNING

2. Attach to job :

impdp datapump/<pwd>@<SID> attach=SYS_IMPORT_FULL_01

3. Kill job:

Import> kill_job
Are you sure you wish to stop this job ([yes]/no): yes