ORA-00054: resource busy and acquire with NOWAIT

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

1.用dba許可權的使用者檢視資料庫都有哪些鎖

SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

USERNAME SID SERIAL# LOGON_TIM
------------------------------ ---------- ---------- ---------
SMP 3261 383 26-AUG-10
SMP 3277 18634 31-AUG-10
SMP 3276 10853 31-AUG-10

2.根據sid檢視具體的sql語句，如果sql不重要，可以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: 3276
old 3: and a.sid=&sid order by piece
new 3: and a.sid=3276 order by piece

SQL_TEXT
----------------------------------------------------------------
DELETE FROM sfcs_sn_site_statistics
WHERE org_id = 51


AND sn_key IN (SELECT sn_key
FROM wip_runcard


WHERE org_id = 51
AND time BETWEEN


SQL_TEXT
----------------------------------------------------------------
to_date('2009-6-2 00:00:00',
'yyyy-mm-dd hh

24:mi:ss') AND to_date('2009-7-1 23:59:59',


'yyyy-mm-dd hh24:mi:ss'))
AND ROWNUM < 2000


6 rows selected.

3.kill該事務

SQL> alter system kill session(3277,18634);
alter system kill session(3277,18634)
*
ERROR at line 1:
ORA-00026: missing or invalid session ID


SQL> alter system kill session '3277,18634';
alter system kill session '3277,18634'
*
ERROR at line 1:
ORA-00031: session marked for kill


4.查出os中對應的pid

SQL> select spid, osuser, s.program
2 from v$session s,v$process p
3 where s.paddr=p.addr and s.sid=&sid;
Enter value for sid: 3277
old 3: where s.paddr=p.addr and s.sid=&sid
new 3: where s.paddr=p.addr and s.sid=3277

SPID OSUSER
------------ ------------------------------
PROGRAM
------------------------------------------------
18352 Proto
toad.exe

5.從OS中KILL SESSION

SQL> host kill -9 18352

6.drop table

SQL> drop table sfcs_sn_site_statistics_arv;

Table dropped.

[@more@]