How to get the list of locks with Oracle

Detection

To be executed as SYSTEM :

select session_id
,      serial#
,      c.status
,      substr(oracle_username,1,20) User_ORA
,      os_user_name User_os
,      substr(object_name,1,20) Objet
,      substr(decode(a.locked_mode,
              0, 'None',           
              1, 'Null',           
              2, 'Row-S (SS)',     
              3, 'Row-X (SX)',     
              4, 'Share',          
              5, 'S/Row-X (SSX)',  
              6, 'Exclusive',      
       to_char(a.locked_mode)),1,20) Mode_Lock
,      program Programme
   from v$locked_object a
   ,    dba_objects b
   ,    v$session c 
  where a.object_id = b.object_id
  and c.sid=a.session_id

Kill a locked session

alter system kill session 'SESSION_ID,SERIAL#';

Published 15th Mar 2011 // #oracle #tips




comments powered by Disqus