oracle數(shù)據(jù)庫死鎖怎么解決

Oracle數(shù)據(jù)庫出現(xiàn)死鎖的時(shí)候可以按照以下處理步驟加以解決：

創(chuàng)新互聯(lián)主營三水網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,手機(jī)APP定制開發(fā),三水h5小程序定制開發(fā)搭建,三水網(wǎng)站營銷推廣歡迎三水等地區(qū)企業(yè)咨詢

第一步：嘗試在sqlplus中通過sql命令進(jìn)行刪除，如果能夠刪除成功，則萬事大吉！但通常情況下，出現(xiàn)死鎖時(shí)，想通過命令行或者通過Oracle的管理工具刪除有死鎖的session，oracle只會(huì)將該session標(biāo)記為killed，但無法清除掉，往往需要通過第二步在操作系統(tǒng)層級(jí)進(jìn)行刪除！

Connected?to?Oracle9i?Enterprise?Edition?Release?9.2.0.1.0?

Connected?as?quik

SQL?select?xidusn,?object_id,?session_id,?locked_mode?from?v$locked_object;?--查死鎖的對(duì)象，獲取其SESSION_ID

XIDUSN?OBJECT_ID?SESSION_ID?LOCKED_MODE

----------?----------?----------?-----------

10?30724?29?3

10?30649?29?3

SQL?select?username,sid,serial#?from?v$session?where?sid=29;?--根據(jù)上步獲取到的sid查看其serial#號(hào)

USERNAME?SID?SERIAL#

------------------------------?----------?----------

QUIK?29?57107

SQL?alter?system?kill?session?'29,57107';?--刪除進(jìn)程，如已經(jīng)刪除過，則會(huì)報(bào)ora-00031的錯(cuò)誤；否則oracle會(huì)將該session標(biāo)記為killed狀態(tài)，等待一段時(shí)間看能否會(huì)自動(dòng)消失，如長時(shí)間消失不掉，則需要做后續(xù)步驟

alter?system?kill?session?'29,57107'

ORA-00031:?session?marked?for?kill

SQL?select?pro.spid?from?v$session?ses,v$process?pro?where?ses.sid=29?and?ses.paddr=pro.addr;?--查看spid號(hào)，以便在操作系統(tǒng)中根據(jù)該進(jìn)程號(hào)刪除進(jìn)程

SPID

------------

2273286

第二步：進(jìn)入操作系統(tǒng)進(jìn)行刪除進(jìn)程，本示例的操作系統(tǒng)是IBM aix。

login:?root?--錄入用戶名

root's?Password:?--錄入密碼

*******************************************************************************

*?*

*?*

*?Welcome?to?AIX?Version?5.3!?*

*?*

*?*

*?Please?see?the?README?file?in?/usr/lpp/bos?for?information?pertinent?to?*

*?this?release?of?the?AIX?Operating?System.?*

*?*

*?*

*******************************************************************************

Last?unsuccessful?login:?Fri?Apr?23?14:42:57?BEIDT?2010?on?/dev/pts/1?from?10.73

.52.254

Last?login:?Fri?Apr?23?15:27:50?BEIDT?2010?on?/dev/pts/2?from?10.73.52.254

#?ps?-ef|grep?2273286?--查看進(jìn)程詳情

root?2289864?2494636?0?17:07:15?pts/1?0:00?grep?2273286

oracle?2273286?1?0?14:38:24?-?0:21?oracleQUIK?(LOCAL=NO)

#?kill?-9?2273286?--刪除進(jìn)程，小心操作，別寫錯(cuò)進(jìn)程號(hào)，如果oracle的關(guān)鍵進(jìn)程被刪，數(shù)據(jù)庫會(huì)崩潰的！

#?ps?-ef|grep?2273286?--再次查看

root?2289864?2494636?0?17:07:15?pts/1?0:00?grep?2273286

For?Windows,?at?the?DOS?Prompt:?orakill?sid?spid

For?UNIX?at?the?command?line?kill?–9?spid

查詢oracle 數(shù)據(jù)庫里有哪些表鎖死

用如下語句查詢鎖死的表：

select?p.spid,

a.serial#,

c.object_name,

b.session_id,

b.oracle_username,

b.os_user_name

from?v$process?p,?v$session?a,?v$locked_object?b,?all_objects?c

where?p.addr?=?a.paddr

and?a.process?=?b.process

and?c.object_id?=?b.object_id;

其中object_name就是被鎖的表名，如圖：

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)

)

怎么查看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)

)

如何查看oracle數(shù)據(jù)庫死鎖

1、?用SYS登陸TOAD

2、打開菜單Database-Monitor-SessionBrowser

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

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

如何查詢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ā)生了，注意是行級(jí)等待，而在表級(jí)沒有死鎖或等待

select * from v$lock where block0 ;

文章題目：怎么查詢oracle死鎖,oracle數(shù)據(jù)庫查詢死鎖
文章網(wǎng)址：http://chinadenli.net/article6/hsjiog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、網(wǎng)站排名、建站公司、品牌網(wǎng)站設(shè)計(jì)、域名注冊(cè)、網(wǎng)站導(dǎo)航

廣告

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