Hi all!
There is one thing that is always true for any version of the Oracle Database: the audit trail simply keeps growing and growing.
Some important facts in this regard:
– It’s based on activity, not the amount of data changed.
– The default location of the database audit trail is the AUD$ table, in the SYSTEM tablespace.
– As you know, If this tablespace gets filled up there could be serious consequences.
– The same concern is true for FGA_LOG$, which is located in the SYSAUX tablespace.
Ok, so if you could move these two tables to specific tablespaces, the root problem would be solved, right?
From 11g R2 on you can move the some or all audit trails to other tablespaces you choose. How?
To move the AUD$:
begin dbms_audit_mgmt.set_audit_trail_location( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, audit_trail_location_value => 'AUDIT_TBS'); end; /
To move the FGA_LOG$:
begin dbms_audit_mgmt.set_audit_trail_location( audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std, audit_trail_location_value => 'AUDIT_TBS'); end; /
There is only one important thing to keep in mind: This tablespace needs to be online when the database is open. Otherwise, if any auditable operation try to be executed, you’ll get an ORA-02002.
This is something recommendable if you are setting the Auditing for your database. This is also something included Oracle in scripts like the Exacheck.
Hope it helps!
Cheers!