oracle 互鎖的sql
SELECT SID, DECODE (BLOCK, 0, 'NO', 'YES') blocker,
DECODE (request, 0, 'NO', 'YES') waiter
FROM v$lock
WHERE request > 0 OR BLOCK > 0
ORDER BY BLOCK DESC;
SELECT 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',
1, 'Blocking',
2, 'Global',
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;
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
DECODE (request, 0, 'NO', 'YES') waiter
FROM v$lock
WHERE request > 0 OR BLOCK > 0
ORDER BY BLOCK DESC;
SELECT 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',
1, 'Blocking',
2, 'Global',
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;
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/90618/viewspace-1142661/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [zt] Oracle與SQL Server的互連OracleSQLServer
- ORACLE SQL的查詢表的鎖的情況OracleSQL
- 檢視處理Oracle中被鎖物件的SQLOracle物件SQL
- 查詢 Oracle 使用者鎖的DLL SQL(轉)OracleSQL
- ORACLE 鎖表的解決方法及查詢引起鎖表SQL語句[轉]OracleSQL
- ORACLE 鎖表的解決方法及查詢引起鎖表SQL語句方法OracleSQL
- oracle的TM鎖、TX鎖Oracle
- smon程式互為死鎖案例--oracle一個bug處理Oracle
- oracle的鎖Oracle
- SQL Server 的死鎖SQLServer
- SQL奇遇記:解鎖 SQL 的秘密SQL
- Oracle的鎖表與解鎖Oracle
- SQL Server 鎖SQLServer
- Oracle的TX鎖(行級鎖、事務鎖)Oracle
- Oracle中的鎖Oracle
- ORACLE 鎖的概念Oracle
- ORACLE鎖的管理Oracle
- 【鎖】Oracle鎖系列Oracle
- oracle 鎖表、解鎖的語句Oracle
- SQL Server死鎖SQLServer
- SQL鎖機制SQL
- sql server死鎖的問題SQLServer
- oracle檢視被鎖的表和解鎖Oracle
- oracle鎖Oracle
- oracle鎖阻塞的分析Oracle
- ORACLE鎖的總結Oracle
- ORACLE鎖的管理(zt)Oracle
- oracle鎖的管理(轉)Oracle
- Oracl 發生鎖表--解鎖sqlSQL
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- 檢視oracle被鎖的表是誰鎖的Oracle
- oracle 查詢鎖 && 解鎖Oracle
- Oracle查詢鎖、解鎖Oracle
- oracle的TM鎖、TX鎖知識完全普及Oracle
- SQL Server中的事務與鎖SQLServer
- 關於 TX 鎖的兩句sqlSQL
- 查詢session被鎖的sql(轉)SessionSQL
- SQL Server 中的鎖定介紹SQLServer