eMarcel.com

Oracle Database, Fusion Middleware, Linux

Identifying locked objects and killing the session in DB

Common error: ORA-00054: resource busy and acquire with NOWAIT specified

To identify all locked sessions in an Oracle database:

Query 1:

  SELECT oracle_username || ' (' || s.osuser || ')' username,
         s.sid || ',' || s.serial# sess_id,
         owner || '.' || object_name object,
         object_type,
         DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
         DECODE (v.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 (lmode))
            mode_held
    FROM v$locked_object v,
         dba_objects d,
         v$lock l,
         v$session s
   WHERE     v.object_id = d.object_id
         AND v.object_id = l.id1
         AND v.session_id = s.sid
ORDER BY oracle_username, session_id;

 

Query 2:

SELECT c.owner,
  c.object_name,
  c.object_type,
  b.sid,
  b.serial#,
  b.STATUS,
  b.osuser,
  b.machine
FROM v$locked_object a ,
  v$session b,
  dba_objects c
WHERE b.sid     = a.session_id
AND a.object_id = c.object_id;

 

Output example:

OWNER	 OBJECT_NAME	SID,SERIAL OBJECT      OBJECT_TYPE   STATUS	     MODE_HELD
SCHEMA_1 (user.test)	738,9076   SCHEMA1.EMP TABLE	     NOT    Blocking Share

Get the SID and SERIAL values of the session you want to kill.

To kill the session in an oracle database:

SQL> ALTER SYSTEM KILL SESSION '579, 703' IMMEDIATE;

This is it!

(Visited 360 times, 1 visits today)

,

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close