Wednesday 23 October 2013

How to find the blocking session

/* Formatted on 10/23/2013 12:29:39 PM (QP5 v5.114.809.3010) */
  SELECT   l.inst_id,
           SUBSTR (L.ORACLE_USERNAME, 1, 8) ORA_USER,
           L.SESSION_ID SID,
           S.serial#,
           SUBSTR (O.OWNER || '.' || O.OBJECT_NAME, 1, 40) OBJECT,
           P.SPID OS_PID,
           DECODE (L.LOCKED_MODE,
                   0, 'NONE',
                   1, 'NULL',
                   2, 'ROW SHARE',
                   3, 'ROW EXCLUSIVE',
                   4, 'SHARE',
                   5, 'SHARE ROW EXCLUSIVE',
                   6, 'EXCLUSIVE',
                   NULL)
              LOCK_MODE
    FROM   sys.GV_$LOCKED_OBJECT L,
           DBA_OBJECTS O,
           sys.GV_$SESSION S,
           sys.GV_$PROCESS P
   WHERE       L.OBJECT_ID = O.OBJECT_ID
           AND l.inst_id = s.inst_id
           AND L.SESSION_ID = S.SID
           AND s.inst_id = p.inst_id
           AND S.PADDR = P.ADDR(+)
ORDER BY   l.inst_id


select * from fnd_conc_req_summary_v where status_code='R'

kill session:

select 'Alter system kill session ''' || sid || ','|| serial# ||''''||';' "session" , process from v$session
where sid='&sid';

No comments:

Post a Comment