How To Move AUD$ Table To Another Tablespace Using DBMS_AUDIT_MGMT

Auditing is always about accountability, and is frequently done to protect and preserve privacy for the information stored in databases. Concern about privacy policies and practices has been rising steadily with the ubiquitous use of databases in businesses and on the Internet. Oracle Database provides a depth of auditing that readily enables system administrators to implement enhanced protections, early detection of suspicious activities, and finely-tuned security responses.

If your AUD$ table is in SYSTEM and SYSTEM tablespace, Then it is advised to move the AUD$ to a dedicated tablespace.

Use 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

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Anuradha’s LinkedIn: https://goo.gl/xsgSbb

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp