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@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/789833/viewspace-1037642/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/789833/viewspace-1037642/