Ameer Khan

By default oracle stores audit data in system tablespace. Most of the customers tend to keep it in default tablespace but as a best practice its best to move the AUD$ table in a non system tablespaces.

Step 1:-To find out existing tablespace of AUD$ table:-

SQL> select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 as Size from dba_segments where segment_name=’AUD$’;

OWNER           SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME       Size

————— ——————– —————— ————— ———-

SYS             AUD$                 TABLE              SYSTEM               10.0625

Step 2:- Create a tablespace to move AUD$ table data

SQL> create tablespace TBS_AUDIT datafile ‘D:\ORACLE\ORADATA\ORCL\TS_AUDIT01.DBF’ size 15G autoextend on next 1G maxsize unlimited;

Step 3:- UseDBMS_AUDIT_MGMT procedure to move the data into newly created tablespace

SQL> BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

audit_trail_location_value => ‘TBS_AUDIT’);

END;

/

PL/SQL procedure successfully completed.

Step 4: Check the AUD$ tablespace

SQL> 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       Size

————— ——————– —————— ————— ———-

SYS             AUD$                 TABLE              TBS_AUDIT              10.0625