1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
reach.santanubanerjee

Common error: ora-00054: resource busy and acquire with nowait specified 2014-02-15

Common error: ora-00054: resource busy and acquire with nowait specified

  1. reach.santanubanerjee
    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;