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#;
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.
No comments:
Post a Comment