〔原创〕查表被谁锁住的脚本。
该脚本可以查现在谁锁住表，以及谁在等待该表。
--
-- 查现在谁锁住表，以及谁在等待该表。
-- lock.sql
-- pURPLEfox
--
SELECT /*+ choose */
bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.sid "SID", ws.sid "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (
wk.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 Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (
hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (
wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE (
hk.BLOCK,
0, 'NOT Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
)
blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.sid = bs.sid(+)
AND wk.sid = ws.sid(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1
/
Reply:
试试这个：
SELECT s.username, s.sid,
DECODE (
l.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',
'DS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table'
) ltype,
o.object_name,
DECODE (
l.lmode,
2, 'Row-S(SS)',
3, 'Row-X(SX)',
4, 'Share',
5, 'S/Row-X(SSX)',
6, 'Exclusive',
'Other'
) mode_held
FROM dba_objects o, v$session s, v$lock l
WHERE s.sid = l.sid AND o.object_id = l.id1;
Reply:
第一个脚本是查询某用户锁表后有其他用户在等待加锁用户释放表，在加锁用户释放该表后其他才能继续操作，在某些情况下管理员可以根据实际情况对锁表的用户进行相应调整（终止该对话或继续等待），不是用于查询目前谁进行锁表操作。
你的在查询的时候没有用户在等待加锁用户释放资源，所以显示未选定行，若你只是要查询目前谁进行锁表操作，可以用第二个脚本，或者查查几天前的另一个脚本，若找不到，我再上传。
