Thursday, January 15, 2009

How to move audit table out of SYSTEM tablespace

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 '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;


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.

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

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

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

No comments:

Post a Comment