Oracle鎖處理、解鎖方法
1、查詢鎖情況
select sid,serial#,event,BLOCKING_SESSION from v$session where event like '%TX%';
2、根據(jù)SID查詢具體信息(可忽略)
select sid,serial#,username,machine,blocking_session from v$session where sid=<SID>;
3、殺掉會(huì)話
#根據(jù)1和2中查到的SID和SERIAL# 定位會(huì)話,并殺掉
ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL>' IMMEDIATE;
或
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>';
附件:
#查詢阻塞腳本
col waiting_session for a20
col lock_type for a15
col mode_requested for a10
col mode_held for a10
col lock_id1 for a10
col lock_id2 for a10
set linesize 120
set pagesize 999
with dba_locks_cust as
(SELECT inst_id||'_'||sid session_id,
DECODE (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',
TYPE)
lock_type,
DECODE (lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
TO_CHAR (lmode))
mode_held,
DECODE (request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
TO_CHAR (request))
mode_requested,
TO_CHAR (id1) lock_id1,
TO_CHAR (id2) lock_id2,
ctime last_convert,
DECODE (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 (block))
blocking_others
FROM gv$lock
),
lock_temp as
(select * from dba_locks_cust),
lock_holder as
(
select w.session_id waiting_session,
h.session_id holding_session,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from lock_temp w, lock_temp h
where h.blocking_others in ('Blocking','Global')
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2
),
lock_holders as
(select waiting_session,holding_session,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
from lock_holder
union all
select holding_session, null, 'None', null, null, null, null
from lock_holder
minus
select waiting_session, null, 'None', null, null, null, null
from lock_holder
)
select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
總結(jié)
以上所述是小編給大家介紹的Oracle鎖處理、解鎖方法,希望對(duì)大家有所幫助,如果大家有任何疑問(wèn)請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持!
相關(guān)文章
Oracle中獲取會(huì)話信息的兩個(gè)函數(shù)分享
這篇文章主要介紹了Oracle中獲取會(huì)話信息的兩個(gè)函數(shù)分享,本文分別講解了USERENV和兩個(gè)函數(shù)sys_context,并對(duì)可以獲取哪些信息作了介紹,需要的朋友可以參考下2014-12-12
Oracle數(shù)據(jù)庫(kù)中l(wèi)ead和lag函數(shù)用法示例
lag與lead函數(shù)是跟偏移量相關(guān)的兩個(gè)分析函數(shù),通過(guò)這兩個(gè)函數(shù)可以在一次查詢中取出同一字段的前N行的數(shù)據(jù)(lag)和后N行的數(shù)據(jù)(lead)作為獨(dú)立的列,這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)中l(wèi)ead和lag函數(shù)用法的相關(guān)資料,需要的朋友可以參考下2024-06-06
Oracle查詢當(dāng)前的crs/has自啟動(dòng)狀態(tài)實(shí)例教程
當(dāng)我們開(kāi)啟或者關(guān)閉自啟動(dòng)后,我們?nèi)绾尾榭串?dāng)前CRS 是處于enable還是處于disable中呢?下面這篇文章主要給大家介紹了關(guān)于Oracle如何查詢當(dāng)前的crs/has自啟動(dòng)狀態(tài)的相關(guān)資料,需要的朋友可以參考下2018-11-11
有關(guān)Oracle數(shù)據(jù)庫(kù)的備份情況
有關(guān)Oracle數(shù)據(jù)庫(kù)的備份情況...2007-03-03
關(guān)于oracle邏輯備份exp導(dǎo)出指定表名時(shí)需要加括號(hào)的問(wèn)題解析
Oracle?的exp、imp、expdp、impdp命令用于數(shù)據(jù)庫(kù)邏輯備份與恢復(fù),這篇文章主要介紹了oracle邏輯備份exp導(dǎo)出指定表名時(shí)需要加括號(hào)嗎,本文給大家詳細(xì)講解,需要的朋友可以參考下2023-04-04
Oracle11g r2 卸載干凈重裝的詳細(xì)教程(親測(cè)有效已重裝過(guò))
Oracle 的安裝和卸載相較于其他 mysql 要麻煩些,小編特此分享一篇教程關(guān)于Oracle11g 徹底卸載干凈并重新安裝,有需要的朋友可以參考下本文2021-06-06
Linux系統(tǒng)下導(dǎo)出ORACLE數(shù)據(jù)庫(kù)出現(xiàn)Exporting questionable statistics.錯(cuò)誤 處理
Linux系統(tǒng)下導(dǎo)出ORACLE數(shù)據(jù)庫(kù)出現(xiàn)Exporting questionable statistics.錯(cuò)誤 處理...2007-03-03

