Steps to move the Audit table from SYSTEM tablespace to newly created tablespace.
• Audit trail tables called SYS.aud$ and SYS.fga_log$ keeps all Audit records in the Oracle database and these tables are owned by SYS and system tablespace is default tablespace for them.
• System tablespace grows day by day because of these Audit trail records.It is advised to move the AUD$ table to a dedicated tablespace.
• From Oracle 11g Release 2 allows better management of the audit trail using the DBMS_AUDIT_MGMT package.
Check the current tablespace for audit table
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024/1024 from dba_segments where segment_name=’AUD$’;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024/1024
———- ————— —————————————————— ————————- ——————–
SYS AUD$ TABLE SYSTEM 3.189
Use DBMS_AUDIT_MGMT procedure to move AUD$ table to AUDIT_DATA tablespace
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘AUDIT_DATA’);
END;
/
Query to view whether tablespace has been moved from system to AUDIT_DATA
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024/1024 from dba_segments where segment_name=’AUD$’;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024/1024
———- ————— —————————————————— ————————- ——————–
SYS AUD$ TABLE AUDIT_DATA 3.189
