Post a Comment On: Oracle Help

Query All the Locked sessions using below SQL and based on your OBJECT find the session locking it

SELECT  l.inst_id, 
    SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER,  
    SUBSTR(L.SESSION_ID,1,3) 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 

Kill the session by below ALTER statement. To perform this operations you need to have full db access, if you face issue, check with your DBA.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;  




at erp, erp, oracleapps, oracleapps erp, oracle erp, oracle ebusiness, oracle application,   
about oracle, oracle developer, oracle jobs, finance software, finance accounting accounting, r12 oracle, what is oracle database, oracle database, Oracle 11g, 11g database