建站
咨詢
售后
建站咨詢
新聞中心
oracle數(shù)據(jù)庫死鎖怎么解決
Oracle數(shù)據(jù)庫出現(xiàn)死鎖的時候可以按照以下處理步驟加以解決：
創(chuàng)新互聯(lián)主營三水網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,手機APP定制開發(fā),三水h5小程序定制開發(fā)搭建,三水網(wǎng)站營銷推廣歡迎三水等地區(qū)企業(yè)咨詢
第一步：嘗試在sqlplus中通過sql命令進行刪除，如果能夠刪除成功，則萬事大吉！但通常情況下，出現(xiàn)死鎖時，想通過命令行或者通過Oracle的管理工具刪除有死鎖的session，oracle只會將該session標記為killed，但無法清除掉，往往需要通過第二步在操作系統(tǒng)層級進行刪除！
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;?--查死鎖的對象，獲取其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#號
USERNAME?SID?SERIAL#
------------------------------?----------?----------
QUIK?29?57107
SQL?alter?system?kill?session?'29,57107';?--刪除進程，如已經(jīng)刪除過，則會報ora-00031的錯誤；否則oracle會將該session標記為killed狀態(tài)，等待一段時間看能否會自動消失，如長時間消失不掉，則需要做后續(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號，以便在操作系統(tǒng)中根據(jù)該進程號刪除進程
SPID
------------
2273286
第二步：進入操作系統(tǒng)進行刪除進程，本示例的操作系統(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?--查看進程詳情
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?--刪除進程，小心操作，別寫錯進程號，如果oracle的關(guān)鍵進程被刪，數(shù)據(jù)庫會崩潰的！
#?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、查看所有會話，是否存在鎖定資源的情況！
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 ;
網(wǎng)站題目：怎么查詢oracle死鎖,oracle數(shù)據(jù)庫查詢死鎖
瀏覽地址：http://www.ef60e0e.cn/article/hsjiog.html
