Move SYS.AUD$ table out of SYSTEM tablespace

How to move the database audit trail tables(  SYS.AUD$ & SYS.FGA_LOG$) to another tablespace using DBMS_AUDIT_MGMT?
-------------------------------------------------------

Note : This move operation can be performed even when the database is up and an audit trail is being written

1) Check the current tablespace of the audit trail tables:

CONN / AS SYSDBA

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name; 2

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           SYSAUX
FGA_LOG$                       SYSAUX


2) Check the current size of two tables:

select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');

SEGMENT_NAME         SIZE_IN_MEGABYTES
-------------------  -----------------------------
AUD$                 12
FGA_LOG$             .0625

3) Create a new tablespace and make sure that it's size is large enough for the tables that will be moved:

SQL> create tablespace audit_tbs datafile '/u01/app/oracle/oradata/d1v11202/audit_tbs1.dbf' size 100M autoextend on;

Tablespace created.

4) Move the audit trail tables using procedure DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION.

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.

4)  Check  whether the tables were moved successfully:

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME              TABLESPACE_NAME
----------------------- ------------------------------
AUD$                     AUDIT_TBS
FGA_LOG$                 AUDIT_TBS

NOTE :
=======
The requirement for the introduction of this package came from Audit Vault. Currently it is not supported to use the DBMS_AUDIT_MGMT package on a pre 11gR2(11.2.0.1)
instance which is not an Audit Vault source database. Its features include the purge of audit records and can be used to move the audit tables AUD$ and FGA_LOG$ to another tablespace.

Audit Trails with dbms_audit_mgmt, which has been officially introduced/supported in Oracle 11g R2.

The DBMS_AUDIT_MGMT package is installed by default in Oracle 11.2, and in patchsets 10.2.0.5 and 11.1.0.7 but has also been made available as a separately installable patch for other versions.

----------------

Script to move SYS.AUD$ table out of SYSTEM tablespace
-------------------------------------------------------
- Restart the database with audit_trail=NONE before running the script ---

create tablespace "AUDIT"
   datafile '$HOME/data/aud01.dbf' size 500k
      default storage (initial 100k next 100k pctincrease 0) ;

create table audx tablespace "AUDIT"
   storage (initial 50k next 50k pctincrease 0)
      as select * from aud$ where 1 = 2 ;

rename AUD$ to AUD$$ ;

rename audx to aud$ ;

create index i_aud2
  on aud$(sessionid, ses$tid)
    tablespace "AUDIT" storage(initial 50k next 50k pctincrease 0);