Moving Audit Table Out Of SYSTEM Tablespace

Database auditing is the process of recording, monitoring and reporting of the actions performed on a database. AUD$ is the underlying table that holds all of the system auditing information which resides in SYSTEM tablespace. We keep on deleting and truncating the Aud$ table so that it doesnot grow large. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace. 

Until 11g, the way to move out SYS.AUD$ is not supported. If  we want to do it then we to do it manually . In 11g we can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION  provided with the DBMS_MGMT package .So below are the steps .

1.) Connect to database as SYS user.

C:\> sqlplus sys/xxxx@noida  as sysdba

2.) Create a tablespace for audit file as

SQL>create tablespace aud_tbs datafile 'C:\app\Neerajs\oradata\noida/audit.dbf'  size 10M autoextend on ;

3.) Create a table inside aud_tbs tablespace as

SQL> create table aud_tab tablespace aud_tbs  as select * from sys.aud$ where 1=2 ;

4.) Rename the Original Audit table as 

SQL> rename aud$ to aud$_org ;

5.) Rename table  aud_tab  to AUD$

SQL> rename aud_tab  to aud$ ;

Hence, auditing record will be stored in the aud_tbs tablespace .Further, we can create an index on the aud_tbs table for quick access .

Enjoy     :-)