Oracle对表解锁的方法

ORA-00054: resource busy and acquire with NOWAIT specified

出现以上问题，表被锁住，不能进行delete和update，只能select，请问如何解决？

commit or rollback

这个出错，因为你的session 执行了select .... for update of nowait 语句。一个ROWlevel Locking is obtained by this session
when another session is running the same query , it failed because of this locking .

Solution :

1. In the first session , specify rollback or commit
2. If you can not find which session is locking the table . query
Using this query to found out :

select substr(object_name,1,30),substr(os_user_name,1,20) from v$locked_object,user_objects
where v$locked_object.object_id = user_objects.object_id

3. If the session is hanging , you can use OEM to kill the session (find the session id and serial# in v$sessions ) or specify the
alter system kill session /'sid , serial#/' to perform the job

good luck

1.一般性是有人是用了for update锁表才有这个现象。
解决方法有两个，那个锁表的人commit或rollback，释放锁
或者直接找到这个session，kill it

If you can not find who is locking the table , use follow query
select substr(object_name,1,30),substr(os_user_name,1,20) from v$locked_object,user_objects
where v$locked_object.object_id = user_objects.object_id

kill the session



kill the session



如果我的机器只装了一个客户端，但是有admin的权力，在客户端有什么办法吗？

那一样啊，以上的方法都可以

你有TOAD之类的工具吗？
找到可疑的进程，KILL IT



关注：
我也想知道如何用指令解锁

查锁脚本，效率很高：
set linesize 200
column sid format 999;
column b format 9;
column spid format 999999;
column object_type format a5
column object_name format a30;
column lock_type format a10;
column ctime format 99999
column username format a15
column machine format a20;
column MODULE format a20;
column action format a20;

select V$SESSION.sid,v$session.SERIAL#,v$process.spid,
rtrim(object_type) object_type,rtrim(owner) || /'./' || object_name object_name,
decode(lmode, 0, /'None/',
1, /'Null/',
2, /'Row-S/',
3, /'Row-X/',
4, /'Share/',
5, /'S/Row-X/',
6, /'Exclusive/', /'Unknown/') LockMode,
decode(request, 0, /'None/',
1, /'Null/',
2, /'Row-S/',
3, /'Row-X/',
4, /'Share/',
5, /'S/Row-X/',
6, /'Exclusive/', /'Unknown/') RequestMode
,ctime, block b,
v$session.username,MACHINE,MODULE,ACTION,
decode(A.type,
/'MR/', /'Media Recovery/',
/'RT/',/'Redo Thread/',
/'UN/',/'User Name/',
/'TX/', /'Transaction/',
/'TM/', /'DML/',
/'UL/', /'PL/SQL User Lock/',
/'DX/', /'Distributed Xaction/',
/'CF/', /'Control File/',
/'IS/', /'Instance State/',
/'FS/', /'File Set/',
/'IR/', /'Instance Recovery/',
/'ST/', /'Disk Space Transaction/',
/'TS/', /'Temp Segment/',
/'IV/', /'Library Cache Invalida-tion/',
/'LS/', /'Log Start or Switch/',
/'RW/', /'Row Wait/',
/'SQ/', /'Sequence Number/',
/'TE/', /'Extend Table/',
/'TT/', /'Temp Table/',
/'Unknown/') LockType
from (SELECT * FROM V$LOCK) A, all_objects,V$SESSION,v$process
where A.sid > 6
and object_name<>/'OBJ$/'
and A.id1 = all_objects.object_id
and A.sid=v$session.sid
and v$process.addr=v$session.paddr;


解锁一般是用commit或者rollback释放lock。
如果时间很紧，kill session。

同意

SELECT * FROM V$SESSION;
ALTER SYSTEM KILL SESSION /' , /' ;