怎么查看oracle是否有死鎖

-- 死鎖查詢語句

專注于為中小企業(yè)提供成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)綏陽免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了1000多家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

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', /**//* 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;

查詢發(fā)生死鎖的select語句

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)

)

如何查詢oracle的死鎖？

下面查詢可以查到死鎖，但是注意其實(shí)查詢到的不是真正的死鎖，查詢到的只是是死鎖發(fā)生前的等待條件，真正的死鎖在10G中會(huì)被系統(tǒng)所阻止掉

select t2.username||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_text

from v$locked_object t1,v$session t2,v$sqltext t3

where t1.session_id=t2.sid

and t2.sql_address=t3.address

order by t2.logon_time ;

此外，從V$LOCK的BLOCK字段可以看到，BLOCK0表示有等待發(fā)生了，注意是行級等待，而在表級沒有死鎖或等待

select * from v$lock where block0 ;

oracle 如何查看有沒有死鎖

--?死鎖查詢語句

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',??????????/**//*?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;

查詢發(fā)生死鎖的select語句

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)

)