[ORACLE] Lock 확인 쿼리

Oracle Lock에 대해 깊이 들어가면 끝도 없는것 같다.

대략적으로 아래의 쿼리 두개를 조합하여 사용하면 빠른 시간에 확인이 가능~

(1)
select /*+ ordered / distinct /* 속도를 위해 v$sql을 조인할 경우 중복되는 레코드 제거 */
           s.sid SID, s.SERIAL# Serial, p.spid "OS-Pid", s.username, s.program, w.seconds_in_wait as "W_time(Sec)",
          decode(w.wait_time,0,'Wai-ting', 'Waited') Status, w.ename event,
--              p1text || ':' || decode(event,'latch free',p1raw, to_char(p1)) ||','||
--              p2text || ':' || to_char(p2) ||','|| p3text || ':' || to_char(p3) "Additional Info",
           q.sql_text
from ( select a.*, decode(a.event,'latch free', 'latch free (' ||b.name||')',
                                         'row cache lock', 'row cache lock (' || c.parameter || ')',
                                         'enqueue', 'enqueue ('||chr(bitand(p1, -16777216)/16777215)||
                                                                             chr(bitand(p1,16711680)/65535)||':'||
                                                decode(bitand(p1,65535), 1, 'N', 2, 'SS',3,'SX',4,'S',5,'SSX',6,'X') ||')',
                              a.event ) ename
           from v$session_wait a, v$latchname b, v$rowcache c
         where a.p2 = b.latch#(+) and a.p1 = c.cache#(+) and c.type(+) = 'PARENT'
           and a.event not in ('rdbms ipc message','smon timer','pmon timer','slave wait','pipe get','null event',
                                     'SQL*Net message from client', 'SQL*Net message to client','PX Idle Wait',
                                      'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
                                         'ges remote message', 'wakeup time manager', /* idle event 적절히 수정 */
                                         'lock manager wait for remote message', 'single-task message')
        ) w, v$session s, v$process p, v$sql q
where w.sid = s.sid and s.paddr = p.addr
and s.sql_hash_value = q.hash_value(+) and s.sql_address = q.address(+)
order by w.ename

(2)
select nvl(S.USERNAME,'Internal') username,
       nvl(S.TERMINAL,'None') terminal, program S,
       L.SID||','||S.SERIAL# Kill,
       U1.NAME||'.'||substr(T1.NAME,1,20) tab,
       decode(L.LMODE,1,'No lock',
                      2,'Row Share',
                      3,'Row Exclusive',
                      4,'Share',
                      5,'Share Row Exclusive',
                      6,'Exclusive',null) lmode,
       decode(L.REQUEST,1,'No lock',
                        2,'Row Share',
                        3,'Row Exclusive',
                        4,'Share',
                        5,'Share Row Exclusive',
                        6,'Exclusive',null) request
from V$lock L,
     V$SESSION S,
     SYS.USER$ U1,
     SYS.OBJ$ T1
where L.SID = S.SID
and   T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and   U1.USER# = T1.OWNER#
and   S.TYPE != 'BACKGROUND'
order by 1,2,3,4,5