List of the locked Oracle objects

Oracle locks

It is quite often required to find out which process is locking a particular object and, if necessary, to release the lock.

This function is offered by the Oracle Enterprise Manager but, again, a simple script is handier when a GUI is a burden or the connection is slow. Our script will list the locks in Oracle.

The query joins the tables V$LOCKED_OBJECT with DBA_OBJECTS in order to get the object name and type which being locked; with V$SESSION to find the serial# of the session locking; with V$SQLAREA to find the statement which is holding the lock.

The "ALTER SYSTEM KILL SESSION " necessary to kill the session is then diplayed.


set line 120
column object_name  format a32
column OS_USER_NAME format a12
column orauser      format a12
column sql_text     format a32
column serial#      format 999999
column sid          format 99999
   SELECT OS_USER_NAME, ORACLE_USERNAME AS orauser, s.sid, o.object_name,
   o.object_type, s.serial#, a.sql_text
   FROM v$locked_object l, dba_objects o, v$session s, v$sqlarea a
   WHERE l.object_id = o.object_id
   AND   s.SQL_ADDRESS    = a.address
   AND l.SESSION_ID = s.sid;
   SELECT 'ALTER SYSTEM KILL SESSION '''||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||''';' 

   AS  "Statement to kill"
   FROM v$locked_object l, dba_objects o, v$session s
   WHERE l.object_id = o.object_id
   AND l.SESSION_ID = s.sid;

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good