Oracle 12c and SYSAUX Tablespace Growing Rapidly

Problem:

You recently upgraded to Oracle 12c and your SYSAUX tablespace continues to grow in size. This may be happening because there is a default Unified Audit policy called ORA_SECURECONFIG.

DB Version:  12.1.0.1 and later.

Solution:

Run the following sql to find the SYSAUX tablespace usage:

SQL>  select  count(*) from unified_audit_trail;  COUNT(*) ---------- 555552619 SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where space_usage_kbytes > 0 order by space_usage_kbytes desc;

Purge the UNIFIED_AUDIT_TRAIL table:

BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( audit_trail_type      =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp  =>  FALSE); END; / --Disable the Unified Audit policies that were enabled by default: NOAUDIT POLICY ORA_SECURECONFIG; NOAUDIT POLICY ORA_LOGON_FAILURES;

Or use the last_arch_timestamp, if you don't want to purge all records:

-- Set time: BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(   audit_trail_type     => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,   last_archive_time    => TO_TIMESTAMP('09-SEP-2017 15:00:00.0','DD-MON-RRRR HH24:MI:SS.FF'),   rac_instance_number  => 1); END; / -- Purge old records: BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(   audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,   use_last_arch_timestamp  =>  TRUE); END; /

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.