With unified audit, audit records from all audit sources are written to a consolidated audit trail–AUDSYS.AUD$UNIFIED table or OS files, and exposed through the UNIFIED_AUDIT_TRAIL view.
But why we should change the default tablespace for Unified Auditing ?
Because database audit trails stored in the SYSAUX tablespace, they can fill it up and could start affecting other database operations that rely on the SYSAUX tablespace. In the eventuality of SYSAUX tablespace becoming full, audit record write will spillover to OS audit files and post a message to ALERT LOG so that corrective action can be initiated. If the OS file system space also becomes full, all database operations will start to fail.
We have below options to resolve the issue related to space and database performance due to these challenges:-
Relocate the unified audit trail table to a dedicated tablespace(Non-Default Tablespace)Set a reasonable unified audit trail partition intervalArchive audit records and purge the unified audit trailImproving query performance on the unified audit trail
In this session, We are changing the default tablespace for Unified Auditing Trail.
Step-1 Login to the database and create the tablespace in the DB.
Note:- AUDIT_DATA is the tablespace in my case
Step-2 Changing the default tablespace for the Unified auditing using below script
Step-3 The AUD$UNIFIED is the table where all the records written. Check the default tablespace for this table.
Step-4 Unified_auditing_trail table location is now in new tablespace as shown in below snap
We can check the record count in this table using below script
SQL> col UNIFIED_AUDIT_POLICIES for a50 SQL> col ACTION_NAME for a30 SQL> select unified_audit_policies,action_name,count(*) from unified_audit_trail group by unified_audit_policies,action_name;
Thank you...........Default tablespace has been changed...................................stay and connected.
NEXT–> Automate the purging audit records for Unified Auditing in the Database.
Caution: It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
A. Rawat
Email: 88arawat@gmail.com