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

今天在执行一个drop 表的操作时，报ORA-00054:resource busy and acquire with NOWAIT specified or timeout expired错误信息，

SQL> drop table user_test;
drop table user_test
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

[oracle@test ~]$ oerr ora 54
00054, 00000, "resource busy and acquire with NOWAIT specified or timeout expired"
// *Cause:  Interested resource is busy.
// *Action: Retry if necessary or increase timeout.

按照字面的意思是资源忙被占用了，所以第一时间想到看是哪个会话占用了资源，在做什么操作，数据库此时是不是有锁存在等。

SQL> select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id;

SESSION_ID OWNER         OBJECT_NAME
---------- ------------  ------------------------
1655         test           user_test

发现表user_test上有锁存在，根据session_id查找会话的详细信息：

SQL> SELECT sid, serial#, username, oSUSEr, terminal,program ,action, prev_exec_start FROM v$session where sid = 1655;

通过查询v$session可以查到占用资源的会话详细信息，通过如下命令了可以杀掉占用资源的会话；之后再执行drop操作就没问题了。

SQL> alter system kill session '1655,43249';

System altered.

SQL> drop table user_test;

Table dropped.