Wednesday, December 28, 2016

Purge huge SYS.AUD$, this option always work

Issue:

SYS.AUD$ table in one of my production crossed all thresholds and was of size 300Gb where no delete/truncate statement was working. I did the way mentioned below and all went fine.

Error:

SQL> truncate table sys.aud$;
truncate table sys.aud$
                   *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> //
truncate table sys.aud$
                   *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

Solution:

oracle@host1:/cs/oracle/data $ [ORADB1]sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 28 06:50:07 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> truncate table SYS.AUD$;

Table truncated.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

1 comment: