ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

SQL>  drop table T_ACCOUNT0410 purge;

drop table T_ACCOUNT0410 purge
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效

查看当前活动会话，没有活动会话怎么drop 不了，突然意识到，表上有锁，造成锁的这个会话也不一定是活动的。

       SID    SERIAL# USERNAME                       TERMINAL                       MACHINE                                                          PROGRAM                                          SPID                     SQL_ID        SQL_TEXT1
---------- ---------- ------------------------------ ------------------------------ ---------------------------------------------------------------- ------------------------------------------------ ------------------------ ------------- --------------------------------------------------------------------------------
      2471      31043 DB_MONITOR                     HLB-PC                         WORKGROUP\HLB-PC                                                 plsqlDev.exe                      

查看造成锁的会话：

SQL> select session_id from v$locked_object;
SESSION_ID
----------
      1619

SQL>  SELECT sid, serial#, username, osuser FROM v$session where sid =1619;
       SID    SERIAL# USERNAME                       OSUSER
---------- ---------- ------------------------------ ------------------------------
      1619      18079 DB_MONITOR                     hlb

SQL> alter system kill session '1619,18079';
alter system kill session '1619,18079'
ORA-00031: 标记要终止的会话

数据库层面kill 不了，是不是要在系统层面删除呢

select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));

select 'kill -9 '||spid from v$process where addr in (select paddr from v$session where sid in ( select session_id from v$locked_object));

SQL> 
SQL_TEXT
--------------------------------------------------------------------------------
update t_account0410 set id7=:1
'KILL-9'||SPID
--------------------------------------------------------
kill -9 2508

在系统层面执行，

kill -9 2508

在删除表：

SQL> drop table T_ACCOUNT0410 purge;
Warning: connection was lost and re-established
Table dropped