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: , , , , , , , , , , , , , , , , , , , , , , , ,

4 Responses 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;

  2. Jim says:

    SELECT objects.owner,
    objects.object_name,
    objects.object_type,
    user1.user_name locking_fnd_user_name,
    login.start_time locking_fnd_user_login_time,
    vs.module,
    vs.machine,
    vs.osuser,
    vlocked.oracle_username,
    vs.sid,
    vp.pid,
    vp.spid os_process,
    vs.serial#,
    vs.status,
    vs.saddr,
    vs.audsid,
    vs.process
    FROM fnd_logins login,
    fnd_user user1,
    v$locked_object vlocked,
    v$process vp,
    v$session vs,
    dba_objects objects
    WHERE vs.sid = vlocked.session_id
    AND vlocked.object_id = objects.object_id
    AND vs.paddr = vp.addr
    AND vp.spid = login.process_spid(+)
    AND vp.pid = login.pid(+)
    AND login.user_id = user1.user_id(+)
    –change the table name below
    AND objects.object_name LIKE ‘%’ || upper(‘AP_INVOICES_ALL’) || ‘%’
    AND nvl(vs.status,
    ‘XX’) != ‘KILLED’;

  3. aliimmam says:

    SELECT B.OWNER, B.OBJECT_NAME, A.ORACLE_USERNAME, A.OS_USER_NAME

    FROM V$LOCKED_OBJECT A, ALL_OBJECTS B

    WHERE A.OBJECT_ID = B.OBJECT_ID;

  4. aliimmam says:

    select
    c.owner,
    c.object_name,
    c.object_type,
    b.sid,
    b.serial#,
    b.status,
    b.osuser,
    b.machine
    from
    gv$locked_object a ,
    gv$session b,
    dba_objects c
    where
    b.sid = a.session_id
    and
    a.object_id = c.object_id;

Leave a Reply to aliimmam