Oracle lock 걸린 세션 조회, ip 찾기

lock 걸린 세션 조회, ip 찾기

박제헌2010-04-30 07:17:23주소복사

조회 168  스크랩 0

lock 걸린 세션 한방에 찾기 - 쿼리 튜닝이 필요함  음..

 

SELECT DISTINCT s.username "ORACLE USER" ,
       p.pid "PROCESS ID" ,
       s.SID "SESSION ID" ,
       s.serial# ,
       osuser "OS USER" ,
       p.spid "PROC SPID" ,
       s.process "SESS SPID" ,
       s.lockwait "LOCK WAIT" ,
       C.lock_type ,
       C.mode_held ,
       C.mode_requested
FROM   v$process p,
       v$session s,
       v$access a ,
       (select a.sid,
               decode(a.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', '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', a.type) lock_type,
               decode(a.lmode, 0, 'None', /* Mon Lock equivalent */
                  1, 'Null', /* N */
                  2, 'Row-S (SS)', /* L */
                  3, 'Row-X (SX)', /* R */
                  3, 'Row-X (SX)', /* R */
                  4, 'Share', /* S */
                  5, 'S/Row-X (SSX)', /* C */
                  6, 'Exclusive', /* X */
                  to_char(a.lmode)) mode_held,
               decode(a.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(a.request)) mode_requested,
               to_char(a.id1) lock_id1,
               to_char(a.id2) lock_id2
        from   v$lock a
        where  (id1,id2) in (select b.id1, b.id2
                from   v$lock b
                where  b.id1=a.id1
                and    b.id2=a.id2
                and    b.request>0) ) C
WHERE  a.SID = s.SID
AND    s.SID=C.SID
AND    p.addr = s.paddr
AND   s.username NOT IN ( 'SYS','DBSNMP','SYSMAN') ;