oracle 如何查看有沒有死鎖

--?死鎖查詢語句

成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比廉江網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式廉江網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋廉江地區(qū)。費用合理售后完善，十余年實體公司更值得信賴。

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ù)據(jù)庫死鎖

1、?用SYS登陸TOAD

2、打開菜單Database-Monitor-SessionBrowser

3、查看所有會話，是否存在鎖定資源的情況！

4、?如果有死鎖，可以將此會話Kill掉！

如何查詢oracle的死鎖？

下面查詢可以查到死鎖，但是注意其實查詢到的不是真正的死鎖，查詢到的只是是死鎖發(fā)生前的等待條件，真正的死鎖在10G中會被系統(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)

)

網(wǎng)站題目：oracle如何查死鎖,查看死鎖 oracle
本文URL：http://aaarwkj.com/article18/dsigedp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、ChatGPT、建站公司、網(wǎng)站內(nèi)鏈、網(wǎng)站改版、動態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)