At some point, whether in compliance with some regulation or as a preventive measure, we will use the standard audit and, why not, the fine audit (FGA). Well, if you choose the database as a destination, it will be recorded in tables stored in the SYSTEM tablespace, and sooner or later, it will be filled, and that’s where the problems start.
There are documented solutions and others not so much, but if you have Oracle 10gR2 10.2.0.3 or higher, there is a simple and—what is better—supported way to do it. We are talking about the brand-new package DBMS_AUDIT_MGMT, which allows us, without much effort, to maintain the audit logs, including tasks such as deleting logs, creating jobs for deleting logs, and also to move the aud$ and fga_log$ tables to a user tablespace, which is what we will see now in action.
1. First, we will create the tablespace to which we will migrate the audit tables.
SQL> create tablespace audit_tbs datafile size 100M;Tablespace created.2. Now move the tables to their new destination.
SQL> BEGIN
2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
3 audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
4 audit_trail_location_value => 'AUDIT_TBS' );
5 END;
6 /
PL/SQL procedure successfully completed.3. Verifying that the migration is complete.
SQL> select owner, segment_name, segment_type
2 from dba_segments
3 where tablespace_name = 'AUDIT_TBS';
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ---------------
SYS SYS_LOB0000059750C00028$$ LOBSEGMENT
SYS SYS_LOB0000059750C00013$$ LOBSEGMENT
SYS SYS_IL0000059750C00028$$ LOBINDEX
SYS SYS_IL0000059750C00013$$ LOBINDEX
SYS FGA_LOG$ TABLE
SYS AUD$ TABLE
6 rows selected.Mission accomplished, and in just a few minutes, no more cumbersome and error-prone procedures, welcome simplicity!
To use this package, you must obtain from My Oracle Support the patch corresponding to the version you are using. Remember that they are only available for versions 10.2.0.3 and higher.
Start by checking the note 731908.1 New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information, and then take a look at the documentation, to learn about all the possibilities of this new feature.
