Tablespace SYSAUX
Since tablespace SYSAUX has grown too big, so we query V$SYSAUX_OCCUPANTS to check the usage of AUDSYS in SYSAUX like this:
SQL> show user
USER is "SYS"
SQL> column occupant_name format a30;
SQL> column occupant_desc format a30;
SQL> column size_gb format 999.99;
SQL> select occupant_name, occupant_desc, space_usage_kbytes/1024/1024 size_gb from v$sysaux_occupants where occupant_name = 'AUDSYS';
OCCUPANT_NAME OCCUPANT_DESC SIZE_GB
------------------------------ ------------------------------ -------
AUDSYS AUDSYS schema objects 58.05
It seems that the repository of unified audit trail owned by AUDSYS is taking too much space in tablespace SYSAUX.
In fact, the repository table is AUD$UNIFIED, a partitioned table which stores all audit trails. We usually query it through public synonym UNIFIED_AUDIT_TRAIL.
To purge AUD$UNIFIED, we leverage DBMS_AUDIT_MGMT package and take the following steps to reach our goal.
SET_LAST_ARCHIVE_TIMESTAMP
To keep some latest events, we can set LAST_ARCHIVE_TIMESTAMP to limit the scope of purging.
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSTIMESTAMP - INTERVAL '30' day);
END;
/
In this case, we keep records within 30 days from now.
To add or substract some days from SYSTIMESTAMP, we use a different way from SYSDATE.
Optionally, you can make sure that LAST_ARCHIVE_TIMESTAMP has been set correctly.
CLEAN_AUDIT_TRAIL
We actually purge unnecessary audit trails according to LAST_ARCHIVE_TIMESTAMP.
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/
Remove All Events
To purge all audit trails definitely, we can flag USE_LAST_ARCH_TIMESTAMP as FALSE.
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, USE_LAST_ARCH_TIMESTAMP => FALSE);
END;
/
If it seems to be running slowly, you may consider to speed up the deletion of unified audit trails.
CLEAR_LAST_ARCHIVE_TIMESTAMP
We should reset LAST_ARCHIVE_TIMESTAMP in case of being misunderstood in the future.
BEGIN
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED);
END;
/
Check SYSAUX
Let's check the space again.
SQL> select occupant_name, occupant_desc, space_usage_kbytes/1024/1024 size_gb from v$sysaux_occupants where occupant_name = 'AUDSYS';
OCCUPANT_NAME OCCUPANT_DESC SIZE_GB
------------------------------ ------------------------------ -------
AUDSYS AUDSYS schema objects 4.85
The space is freed.