lock检查

---desc:oracle检查某个表对象上的lock

SELECT username,
schemaname,
se.MACHINE,
se.MODULE,
'ALTER SYSTEM KILL SESSION ' || '''' || se.sid || ',' || se.serial# || ',' || '@' ||
se.inst_id || '''' || ' immediate;' kill_stat
FROM GV$SESSION se, GV$LOCK lk, DBA_OBJECTS obj
where se.INST_ID = lk.INST_ID
and se.SID = lk.SID
and lk.ID1 = obj.OBJECT_ID
and OBJECT_NAME = trim(upper('&object_name'));

---desc：出现tx锁时，在gv$sessoin_blocker能查不到数据的时候。可以用下列语句检查阻塞session

SELECT se.username,
schemaname,se.MACHINE,se.MODULE,
'ALTER SYSTEM KILL SESSION ' || '''' || se.sid || ',' || se.serial# || ',' || '@' ||
se.inst_id || '''' || ' immediate;' kill_stat,'kill -9 '||p.SPID,p.pid,p.PROGRAM
FROM GV$SESSION se,gv$lock lk,gv$process p
where se.INST_ID=lk.INST_ID
and se.PADDR=p.ADDR
and se.SID=lk.sid
and lk.BLOCK=1;

=======================>如何解决 library cache pin 等待《=========================
1.查找哪个对象希望被pin
SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr='&P1RAW'
2.查找造成阻塞的会话和被阻塞的会话
SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='&P1RAW'
AND kglpnreq=0
kglpnreq=0:表示正在持有该对象的会话，造成阻塞的会话
kglpnreq>0:表示求而不得，就是被阻塞的会话
mode 3：排他 mode 2：共享
找出阻塞的会话
An X request (3) will be blocked by any pins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.
--找出阻塞会话并生产kill语句
SELECT 'ALTER SYSTEM KILL SESSION ' || '''' || se.sid || ',' || se.serial# || ',' || '@' ||
se.inst_id || '''' || ' immediate;' kill_stat, kglpnmod "Mode", kglpnreq "Req"
FROM x$kglpn p, gv$session se
WHERE p.kglpnuse=se.saddr
and p.INST_ID=se.inst_id
and p.KGLPNSID=se.sid
AND kglpnhdl='&P1RAW'
AND kglpnreq=0; --kglpnreq=0:表示正在持有该对象的会话，造成阻塞的会话
=======================>如何解决 library cache pin 等待《=========================

=======================>如何解决 library cache lock 等待《=========================
KGLLKREQ=0:表示正在持有该对象的会话，造成阻塞的会话
KGLLKREQ>0:表示求而不得，就是被阻塞的会话
mode 3：排他 mode 2：共享 1:null易碎解析锁
1.找出造成阻塞的会话
PROMPT Blocker Sesssion and Object info

SELECT 'ALTER SYSTEM KILL SESSION ' || '''' || se.sid || ',' || se.serial# || ',' || '@' ||
se.inst_id || '''' || ' immediate;' kill_stat, KGLLKMOD "Mode", KGLLKREQ "Req",KGLLKCNT,KGLNAOBJ,se.sql_id,lk.username
FROM x$kgllk lk, gv$session se
WHERE lk.kgllkses=se.saddr
and lk.INST_ID=se.inst_id
and lk.KGLLKSNM=se.sid
AND KGLLKHDL='&P1RAW'
AND KGLLKREQ=0 -- kglpnreq=0:表示正在持有该对象的会话，造成阻塞的会话

2.找出被阻塞的会话
PROMPT Waiter Sesssion and Object info

SELECT 'ALTER SYSTEM KILL SESSION ' || '''' || se.sid || ',' || se.serial# || ',' || '@' ||
se.inst_id || '''' || ' immediate;' kill_stat, KGLLKMOD "Mode", KGLLKREQ "Req",KGLLKCNT,KGLNAOBJ,se.sql_id
FROM x$kgllk lk, gv$session se
WHERE lk.kgllkses=se.saddr
and lk.INST_ID=se.inst_id
and lk.KGLLKSNM=se.sid
AND KGLLKHDL='&P1RAW'
AND KGLLKREQ>0 ;-- kglpnreq>0:表示求而不得，就是被阻塞的会话
=======================>如何解决 library cache lock 等待《=========================