LOCK TABLES and UNLOCK TABLES Syntax For ORACLE

Share
Identify locked objects :

select object_name, v.session_id SID, v.oracle_username,type,lmode,request
       from v$locked_object v, v$lock l, dba_objects o
       where l.sid=v.session_id
       and v.object_id=o.object_id
       and l.block > 0;

Identify who locks whom :

select
      (select osuser from v$session where sid=a.sid) blocker,
      a.sid,
      (select serial# from v$session where sid=a.sid) serial#,
      ' blocks ',
      (select osuser from v$session where sid=b.sid) blockee,
      b.sid, c.username username
    from v$lock a, v$lock b, v$session c
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2
    and b.sid = c.sid

Kill session :

SQL> alter system kill session 'sid,serial#';

 

 

*********************************************************************************************************************
Also Check : http://aliimmam.com/locking-and-unlocking-tables-in-oracle/
Also Check : http://aliimmam.com/forms-could-not-reserve-record-2-tries-keep-trying/
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as well.

*********************************************************************************************************************

Share

Comments

comments

Tags: , , , , , , , , , , , , , , , , , , , , , , , ,

One Response to “LOCK TABLES and UNLOCK TABLES Syntax For ORACLE”

  1. aliimmam says:

    SELECT S.SID, s.serial#, username U_NAME, owner OBJ_OWNER,
    object_name, object_type, s.osuser,
    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 gv$locked_object v, dba_objects d,
    gv$lock l, gv$session s
    WHERE v.object_id = d.object_id
    AND (v.object_id = l.id1)
    AND v.session_id = s.sid
    ORDER BY username, session_id;

Leave a Reply