我运行select * from ....where ....for update nowait,一段时间后提示:“ORA-00054:resource busy and acquire with NOWAIT specified”请问是何原因啊,谢谢了!这张表只有不到5万条记录。而我在另一张有39万条记录的表执行类似的操作,很快就完成了。两张表都是只删除2条记录。
----查找某用户下的所有锁 select a.*,b.object_name from v$locked_object a ,all_objects b ,v$session c where a.OBJECT_ID=b.object_id AND a.SESSION_ID=c.SID and c.USERNAME='abc'
例: SQL> select username,status,sid,serial# from v$session where username is not null;USERNAME STATUS SID SERIAL# ------------------------------ -------- --------- --------- SYS ACTIVE 11 38493SQL> /USERNAME STATUS SID SERIAL# ------------------------------ -------- --------- --------- TEMP INACTIVE 9 38038 SYS ACTIVE 11 38493SQL> alter system kill session '9,38038';系统已更改。SQL> select username,status,sid,serial# from v$session where username is not null;USERNAME STATUS SID SERIAL# ------------------------------ -------- --------- --------- TEMP KILLED 9 38038 SYS ACTIVE 11 38493SQL>
用sys用户进入,到DBA-->session browser-->lock下面去看,直接点删除就行了
select a.*,b.object_name
from v$locked_object a ,all_objects b ,v$session c
where a.OBJECT_ID=b.object_id
AND a.SESSION_ID=c.SID
and c.USERNAME='abc'
SQL> select username,status,sid,serial# from v$session where username is not null;USERNAME STATUS SID SERIAL#
------------------------------ -------- --------- ---------
SYS ACTIVE 11 38493SQL> /USERNAME STATUS SID SERIAL#
------------------------------ -------- --------- ---------
TEMP INACTIVE 9 38038
SYS ACTIVE 11 38493SQL> alter system kill session '9,38038';系统已更改。SQL> select username,status,sid,serial# from v$session where username is not null;USERNAME STATUS SID SERIAL#
------------------------------ -------- --------- ---------
TEMP KILLED 9 38038
SYS ACTIVE 11 38493SQL>