APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 20-FEB-2013***

GOAL

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

FIX

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.

REFERENCES

NOTE:1299033.1 - Master Note For Oracle Database Auditing
NOTE:731908.1 - New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
NOTE:804624.1 - Known Issues When Using: DBMS_AUDIT_MGMT

How To Move The DB Audit Trails To A New Tablespace

APPLIES TO:

GOAL

FIX

REFERENCES