How to move SYS.AUD$ table to another tablespace using DBMS_AUDIT_MGMT:

Overview:

Database auditing helps meet your database security and compliance requirements. Audit records are written to database tables, operating system (OS) files, or XML files depending on the AUDIT_TRAIL initialization parameter setting.When AUDIT_TRAIL is set to DB, database records are written to the AUD$ and FGA_LOG$ tables in the SYSTEM tablespace. When AUDIT_TRAIL is set to OS, audit records are written to operating system files. When AUDIT_TRAIL is set to XML, audit records are written to operating system files in XML format.

It is important to manage your audit records properly in order to ensure efficient auditing and clean up.The DBMS_AUDIT_MGMT subprograms enable you to efficiently manage your audit trail records.

Below are the steps required to move AUD$ table to another tablespace:

• Check the details for SYS.AUD$ table: 

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

• Create new tablespace.

CREATE tablespace AUD_DATA datafile size 15gb autoextend on;

• Execute DBMS_AUDIT_MGMT procedure to move SYS.AUD$ table to AUD_DATA tablespace

EGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => 'AUD_DATA');

END;

/

• check whether tablespace has been moved from system to AUDIT_DATA or not. 

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