脚本可行,已经试用多次
Column SID FORMAT 9999999 heading "Sess|ID "
COLUMN SERIAL# FORMAT 9999999 heading "Serial# "
COLUMN OBJECT_NAME FORMAT A17 heading "OBJ NAME or|TRANS_ID" Trunc
COLUMN OSUSER FORMAT A10 heading "Op Sys|User ID"
COLUMN USERNAME FORMAT A8
select
B.SID,
C.SERIAL#,
C.USERNAME,
C.OSUSER,
DECODE(B.ID2, 0, A.OBJECT_NAME,\'Trans-\'||to_char(B.ID1)) OBJECT_NAME,
B.TYPE,
DECODE(B.LMODE,0,\'--Waiting--\',
1,\'Null\',
2,\'Row Share\',
3,\'Row Excl\',
4,\'Share\',
5,\'Sha Row Exc\',
6,\'Exclusive\',
\'Other\') "Lock Mode",
DECODE(B.REQUEST,0,\' \',
1,\'Null\',
2,\'Row Share\',
3,\'Row Excl\',
4,\'Share\',
5,\'Sha Row Exc\',
6,\'Exclusive\',
\'Other\') "Req Mode"
from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID = B.ID1
and B.SID = C.SID
and C.USERNAME is not null
and A.object_name like upper(\'inv_rcv_std_rcpt_apis\') --可能被锁的表名
/--3.oracle 解锁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; --查看锁
--alter system kill session \'sid,serial#\'; --把锁给KILL掉 --146为sid,21177为serial# alter system kill session \'146,21177\';还有一种简单方法:
如果你在试用Toad,可以直接切换到“Toad Session Browser”,找到可疑的锁,直接解锁