Oracle Diagnostics: Drop table failed [go]

Turn : From <http://blog.csdn.net/cyxlxp8411/article/details/7775113>

Today , I reported ORA-00054 errors in the drop of a table.

sql> drop table T2;

drop TABLE T2

*

ERROR at line 1:

Ora-00054:resource busy and acquire with NOWAIT specified or timeout expired

after Google, refer to the online master, operation as follows :

1. See what locks the database has with DBA authority users

sql> Select T2.username,t2.sid,t2.serial#,t2.logon_time

2 from V$locked_object t1,v$session T2

3 where T1.session_id=t2.sid order by T2.logon_time;

USERNAME SID serial# Logon_tim

------------------------------ ---------- ---------- ---------

CLS 1 7 23-jul-12

CLS 1 7 23-jul-12

Know that the locked user Cls,sid is 1andserial# is 7

2. depending on the SID view specific SQL statements, if SQL is not important, you can kill

Sql> Select Sql_text from v$session a,v$sqltext_with_newlines b

2 where DECODE (A.sql_hash_value,0,prev_hash_value,sql_hash_value) =b.hash_value

3 and A.sid=&sid order by piece;

Enter value for Sid:1

Old 3:and a.sid=&sid ORDER by piece

New 3:and a.sid=1 ORDER by piece

Sql_text

----------------------------------------------------------------

DELETE from Plan_table WHERE statement_id=:1

3.kill The transaction

Sql> alter system kill session ' 1,7 ';

System altered.

4. this makes it possible to execute other transactional SQL statements.

sql> drop table T2;

Table dropped.

Oracle Diagnostics: Drop table failed [go]