How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT

 If your AUD$ table is in SYSTEM tablespace, then it is advised to move the AUD$ table to a dedicated tablespace like SYSAUX. Oracle 11g Release 2 now allows better management of the audit trail using the DBMS_AUDIT_MGMT package.

Steps To Move AUD$ Table To Different Tablespace

STEP 1: Check the owner and existing tablespace of AUD$ table 

SQL> select owner,segment_name,TABLESPACE_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE from dba_segments where segment_name='AUD$';

OWNER      SEGMENT_NA TABLESPACE_NAME  SEGMENT_TYPE    SEGMENT_SUBTYPE

---------- ---------- ---------------- --------------- ---------------

SYS        AUD$       SYSTEM           TABLE           MSSM

STEP 2: Execute DBMS_AUDIT_MGMT procedure to move AUD$ table to SYSAUX tablespace

BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,–this moves table AUD$ audit_trail_location_value => ‘SYSAUX’); END; /

STEP 3: Check the new tablespace name in which you have moved the AUD$ table 

SQL> select owner,segment_name,TABLESPACE_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE from dba_segments where segment_name='AUD$';

OWNER      SEGMENT_NA TABLESPACE_NAME  SEGMENT_TYPE    SEGMENT_SUBTYPE

---------- ---------- ---------------- --------------- ---------------

SYS        AUD$       SYSTEM           TABLE           MSSM