How To Shrink Table AUD$

The SYSTEM tablespace is created with manual segment allocation and as such it is not possible to run the SHRINK command for the objects that are located within. However, if the AUD$ table is moved to another tablespace (locally managed with automatic segment space management) then it can be shrunk. It is recommended to use shrink on the AUD$ only during a downtime window, since part of the shrink operation will use incompatible locks as referenced in note 242090.1, also because of some issues fixed with shrink in patchset 11.2.0.2 it is not recommended to use shrink in an earlier version than 11.2.0.2.


Note : These steps have been tested on DB that is older to 10G

To move the AUD$ table one can use the DBMS_AUDIT_MGMT package, as documented in:

Note 731908.1- "New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information"


We need to move the AUD$ table to a locally managed tablespace.
To move the table to a locally managed tablespace with ASSM and then shrink it do the following:

1)

conn / as sysdba

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
END;
/

This is manual process of doing it (for pre 11g DB)
2)
 

alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade;

Comment: if Oracle Label Security (OLS) option is installed and enabled the AUD$ table resides in the SYSTEM schema.


3) If needed the table can be moved back to the SYSTEM tablespace:

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'SYSTEM');
END;
/

Audit Trail Management and Cleanup (including DBMS_AUDIT_MGMT package) is a supported feature for all RDBMS  from 11.2. (Standard Edition and Enterprise Edition)