How To Reorganize Audit Trail AUD.SYS$ Table

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described.
1)Enable restricted session.
In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session.

SQL> connect / as sysdba
Connected.

SQL> alter system enable restricted session;
System altered.

Check if sessions are still connected by,
SQL> select sid, serial#, username from v$session;

If necessary kill these sessions with,
SQL> alter system kill session 'sid , serial#';

2)Copy SYS.AUD$ table.
SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$;
You now can take a dump of audit_record.

3)Truncate SYS.AUD$ table.
SQL> truncate table sys.aud$;
Table truncated.

SQL> select count(*) from SYS.AUD$;
COUNT(*)
--------
0

4)Copy the rows back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;

You can also import it if you exported it in step 2.

5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;

Related Post:-