Posts Tagged ‘Australian dollar’

Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile ‘/oracle/TST/datafile/aud01.dbf’ size 500M;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

SQL> select count(*) from aud$; 


Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

SQL> select count(*) from aud$$; 

Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

SQL> select count(*) from aud$; 

Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Read Full Post »