How to truncate Audit table (SYS. AUD$).

Friends, Recently I was trying to Truncate the SYS.AUD$ table, but it won't allow me to do

 the same,because of application is running. (got the error like "resources busy") and found there are lots of DML operation are inserting the record in audit table. 

Use the below command to truncate the audit table.

1) Active and Inactive session.

select status,username,last_call_et from v$session where username is not null;

2) check the long running queries.

col USERNAME for a15
Set lines 2000
set pages 200
col MODULE for a20
col SQL_TEXT for a75
col machine for a20
select distinct s.username,s.sid,s.serial#,round(s.last_call_et/60,2) "MINS_RUNNING",s.module,s.machine,q.sql_text from v$session s
join v$sqlarea q
on s.sql_address(+) = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and s.last_call_et > 1800
and q.sql_text not like '%begin%'
order by sid,serial#;
 

3) Try to Truncate the SYS.AUD$ table.

SQL> Truncate table sys.aud$.

ERROR: ORA-00054: resource busy and acquire with NOWAIT ...

4) Inform Application team for shutdown the application.

5) Shutdown the Database as well.

SQL> shutdown immediate;

6) Restart the database..

SQL> Startup

7) Take the Audit table export backup by using normal EXP command.

$ exp file=EXP_AUD_TABLE_<SID><date>.dmp LOG=EXP_AUD_TABLE_<SID><date>.log Tables=SYS.AUD$ buffer=1000000.

The above command takes some time to export the table data.

8) Truncate audit table to reduce size.

SQL>CONN / as sysdba

SQL>TRUNCATE TABLE sys.aud$;

Inform application team to start the Application.