Logo Background

Oracle Table Locking

  • By on November 12, 2008 | No Comments

    Script that used to detect table locking in Oracle Database and kill the session that lock’s it.

    SQL> SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",
    o.owner, o.object_name, o.object_type , l.session_id
    FROM v$locked_object l, dba_objects o
    WHERE l.object_id = o.object_id
    ORDER BY o.object_id, 1 DESC ;
    SQL> SELECT session_id "sid",SERIAL# "Serial",
    substr(object_name,1,20) "Object",
    substr(os_user_name,1,10) "Terminal",
    substr(oracle_username,1,10) "Locker",
    nvl(lockwait,'active') "Wait",
    decode(locked_mode,
    2, 'row share',
    3, 'row exclusive',
    4, 'share',
    5, 'share row exclusive',
    6, 'exclusive', 'unknown') "Lockmode",
    OBJECT_TYPE "Type"
    FROM
    SYS.V_$LOCKED_OBJECT A,
    SYS.ALL_OBJECTS B,
    SYS.V_$SESSION c
    WHERE
    A.OBJECT_ID = B.OBJECT_ID AND
    C.SID = A.SESSION_ID
    ORDER BY 1 ASC, 5 DESC;

    This would give you all the objects that are locked. Pick the particular record. (It would have the table name)

    SQL> SELECT sid,serial# FROM v$session
    WHERE session_id = <SESSION id FROM above query>;

    This would give you the sid and session-serial required for the below query.

    SQL> ALTER SYSTEM KILL SESSION 'session-id, session-serial';

    This would kill the specific session.

    Previous
    Next
    » Oracle Concurrent Manager Process
Leave a Comment