Friday, April 4, 2014

How to find the locked tables in Oracle?


Execute this Query

SELECT   owner,
         object_name,
         oracle_username || ' (' || vs.status || ')' ora_user,
         os_user_name os_user,
         machine SYSTEM,
         vlo.process unix,
         '''' || vs.SID || ',' || vs.serial# || '''' sid_serial,
         vr.NAME roll_name,
         TO_CHAR (vs.logon_time, 'YYYY/MM/DD HH24:MI:SS') TIME
    FROM v$locked_object vlo,
         dba_objects doj,
         v$session vs,
         v$transaction vt,
         v$rollname vr
   WHERE vlo.object_id = doj.object_id
     AND vs.SID = vlo.session_id
     AND vs.taddr = vt.addr
     AND vt.xidusn = vr.usn
ORDER BY os_user,
         sid_serial,
         object_name

* Pass / Add condition for Object (Table) Name If required.

How to kill a particular session?

ALTER SYSTEM KILL SESSION 'v$session.sid,v$session.serial';