Monday, January 30, 2017

Oracle Apps: Kill a Locked session through Toad


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;  


2 comments:

  1. This is really nice and informative, containing all the information and also having a great impact on the new technology. I just want to thank you for taking the time to share this app with us. Now it's time to get Gutter Services in Florham Park NJ for more information.

    ReplyDelete
  2. Oracle Apps, Toad is a valuable tool for managing and troubleshooting database sessions. One of its capabilities is the ability to kill locked sessions, providing administrators with the means to address performance issues efficiently. Whether it's optimizing landing page design services or resolving other database-related challenges, Toad empowers users to take control and resolve locked session concerns promptly and effectively. With Toad's functionality, administrators can ensure smooth operations and enhance the overall performance of their Oracle Apps environment.

    ReplyDelete

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...