f 碰到Oracle Lock Wait ~ 迪貝之家

碰到Oracle Lock Wait

 

用了10g以上的版本後
最常用的工具就是awr 及addm
以前常用的script都丟掉了
只是最近又幹起了Programmer的工作
你就不太可能像以前幹dba一樣
知道詳細的資料庫連接資料
碰到都是人家已經在工具UI已經弄妥的設定
你只能直接查internal view來解決問題
昨天要refresh整個schema,然後重匯資料
結果有lock,無法清空
臨時間又懶得去翻找以前的東西
感謝Internet
資料隨手可得阿
以前的老dba都雪藏秘珍這種東西啦~~
所以....這些人現在都活不下去了
昨天對我有幫助的是下列這段語法
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
砍掉該session,就能繼續做事了
出處 :
http://www.dba-oracle.com/t_find_oracle_locked_objects.htm

RAC:
select
b.inst_id,
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

alter system kill session 'sid,serial#,@inst_id';


###2021/05/25 找到lock chain####
這適用於有個session 卡住了後續其他的session
砍掉它之後,系統就能跑了
主要是查v$wait_chains
這東西不得了
怎麼以前沒看過




col ses format a15
set lines 180
WITH blocked AS
(SELECT * FROM
(SELECT instance, sid, sess_serial#, blocker_instance,
blocker_sid, blocker_sess_serial#, level lv,
num_waiters, blocker_chain_id
FROM v$wait_chains
CONNECT BY PRIOR sid = blocker_sid
AND PRIOR sess_serial# = blocker_sess_serial#
AND PRIOR INSTANCE = blocker_instance
START WITH blocker_is_valid = 'FALSE'
)
WHERE num_waiters >0 OR blocker_sid IS NOT NULL
)
SELECT instance,
LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#,
b.blocker_instance, b.blocker_sid, b.blocker_sess_serial#
FROM blocked b
hang analysis也可以抓wait chain
SQL> oradebug setmypid
SQL> oradebug -g all hanganalyze 10



























這是8720.trc 的內容


指令alter system kill session '33,1361';
lock wait 就解了

不過底下這傢伙
寫得更精采
讓我不得不佩服
這些人到底去哪裡找到的資料???
The Backdoor Entry to Oracle Database
















看起來,同樣的lock wait chain的情境
無法用hanganalyze 12 來解決
有時間倒是可以研究一下各 level 要看什麼東西
session無法登入的情況
以前是真的碰過一次
當時要是知道這方法
也許就能查明原因了

### 2021/06/01####
今天碰到一個狀況
居然連select_catalog_role的權限都不給
真的是....莫名其妙
只好去找一個一個對應的fixed table的名稱
grant select on sys.gv_$session to steven;
grant select on sys.gv_$locked_object to steven;
grant select on sys.v_$wait_chains to steven;

--Wait Chain---
col ses format a15
set lines 180
WITH blocked AS
(SELECT * FROM
(SELECT instance, sid, sess_serial#, blocker_instance,
blocker_sid, blocker_sess_serial#, level lv,
num_waiters, blocker_chain_id
FROM sys.v_$wait_chains
CONNECT BY PRIOR sid = blocker_sid
AND PRIOR sess_serial# = blocker_sess_serial#
AND PRIOR INSTANCE = blocker_instance
START WITH blocker_is_valid = 'FALSE'
)
WHERE num_waiters >0 OR blocker_sid IS NOT NULL
)
SELECT instance,
LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#,
b.blocker_instance, b.blocker_sid, b.blocker_sess_serial#
FROM blocked b;

--Lock Objects ---
select * from sys.v_$wait_chains ;
select
b.inst_id,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
sys.gv_$locked_object a ,
sys.gv_$session b,
user_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;