How To Move AUD$ Table To Another Tablespace Using DBMS_AUDIT_MGMT - Learnomate Technologies

ANKUSH THAVALI
11 Oct, 2023
0 Comments
47 Secs Read

How To Move AUD$ Table To Another Tablespace Using DBMS_AUDIT_MGMT

If your AUD$ table is in SYTEM tablespace, Then it is advised to move the AUD$ to a dedicated tablespace. Follow the steps to move AUD$.


select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024
------- ------------- ------------------ ------------------------------ ---------------
SYS AUD$ TABLE SYSTEM 16

Use the dbms_audit_mgmt to move the 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;
/

PL/SQL procedure successfully completed.

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


select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024
------- ------------- ------------------ ------------------------------ ---------------
SYS AUD$ TABLE AUDIT_DATA 16

Hope it Helps!