The following are the quick steps to purge the aud$ table.
Starting 10.2.0.1
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, we can move it for maintenance and port it back to SYSTEM tablespace.
We are going to shrink it using DBMS_AUDIT_MGMT package.
1. To move the table to a locally managed tablespace with ASSM and then shrink it do the following
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;
/
2. Shrink the tablespace,
alter table system.aud$ enable row movement;
alter table system.aud$ shrink space cascade;
3. Move back the aud$ to 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;
/
Pre 10.2.0.1
1. Copy the data to another table,
CREATE TABLE BKUP AS SELECT * from sys.aud$ WHERE 1=2;
INSERT INTO BKUP SELECT FROM sys.aud$
2. Export the BKUP table using exp or expdp utility (do not export SYS.AUD$ directly) or if you have space keep the table which will later use to re-import
3. Truncate sys.aud$ table (Truncate uses the DROP STORAGE clause which keeps ‘minextents’ extents, thus only 1 extent)
SQL> connect / as sysdba
SQL> TRUNCATE TABLE sys.aud$;
4. Import the exported table and then insert it in to aud$ table
INSERT INTO sys.aud$ SELECT FROM BKUP
Note: SYS.AUD$ is the only SYS object that should ever be directly modified
-----------------------------------------------------------------------------
Export sys.aud$:
exp system/manager file=aud.dmp tables=sys.aud$;