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
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
Thanks
ReplyDelete