Forum Stats

  • 3,750,344 Users
  • 2,250,158 Discussions
  • 7,866,943 Comments

Discussions

How to see lock on table and query?

Rafi (Oracle DBA)
Rafi (Oracle DBA) Member Posts: 1,305 Silver Badge
edited Feb 22, 2010 7:00AM in General Database Discussions
Hi All,
How do we see lock on table and query?


Thanks,
Rafi
User_0W7FC

Best Answer

  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Accepted Answer
    Yes Rafi,

    It is working fine at my end. See below:

    Opened Session 1 with scott/tiger and:
    update emp set ename='xx' where empno=7499;

    Opened Session 2 with scott/tiger and:
    update emp set ename='xx' where empno=7499;
    <<Its lock here>> This session is locked by above one.

    Opened Session 3 with sys/pw as sysdba and:
    SQL> set serveroutput on
    SQL> BEGIN
      2  dbms_output.enable(1000000);
      3  for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
      4  b.object_name object_name, b.object_type object_type
      5  FROM v$locked_object a, dba_objects b
      6  WHERE xidsqn != 0
      7  and b.object_id = a.object_id)
      8  loop
      9  dbms_output.put_line('.');
     10  dbms_output.put_line('Blocking Session : '||do_loop.session_id);
     11  dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
     12  dbms_output.put_line('Object Type : '||do_loop.object_type);
     13  for next_loop in (select sid from v$lock
     14  where id2 = do_loop.xidsqn
     15  and sid != do_loop.session_id)
     16  LOOP
     17  dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
     18  end loop;
     19  end loop;
     20  END;
     21  /
    .
    Blocking Session : 139
    Object (Owner/Name): SCOTT.EMP
    Object Type : TABLE
    Sessions being blocked : 134
    
    PL/SQL procedure successfully completed.
    HTH
    Girish Sharma
«1

Answers

  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Rafi,
    Script to find locks in the database
    http://www.dbapool.com/dbscripts/script_33.html

    HTH
    Girish Sharma
  • 544167
    544167 Member Posts: 110
    Rafi,

    You can check the v$lock table for any lock on the objects. Following query might help you to find the locking session.

    SQL> select * from v$lock where block > 0;

    Hope this helps.

    Regards,
    -Praveen.
    http://myracle.wordpress.com
  • 753092
    753092 Member Posts: 26
    Hi,

    Use this query to find locks.
    select
    (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
    a.sid || ', ' ||
    (select serial# from v$session where sid=a.sid) sid_serial,
    ' is blocking ',
    (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
    b.sid || ', ' ||
    (select serial# from v$session where sid=b.sid) sid_serial
    from v$lock a, v$lock b
    where a.block = 1
    and b.request > 0
    and a.id1 = b.id1
    and a.id2 = b.id2;
    Regards,
    Sathish
    User_0W7FC
  • Hi,

    You can find out using below query.
    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;
    Regards,
    Vijayaraghavan K
    Vijayaraghavan Krishnan
  • Rafi (Oracle DBA)
    Rafi (Oracle DBA) Member Posts: 1,305 Silver Badge
    Hi,
    There are various types of locks.When I accessing a query I will be using 3 tables so How I should know a lock is there on any of the table.Do I need to check one by one or is there any other?

    I know the query for finding locks in Database.I want to know on specific objects or query?


    Thanks,
    Rafi.
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Check this:

    set serveroutput on
    BEGIN
    dbms_output.enable(1000000);
    for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
    b.object_name object_name, b.object_type object_type
    FROM v$locked_object a, dba_objects b
    WHERE xidsqn != 0
    and b.object_id = a.object_id)
    loop
    dbms_output.put_line('.');
    dbms_output.put_line('Blocking Session : '||do_loop.session_id);
    dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
    dbms_output.put_line('Object Type : '||do_loop.object_type);
    for next_loop in (select sid from v$lock
    where id2 = do_loop.xidsqn
    and sid != do_loop.session_id)
    LOOP
    dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
    end loop;
    end loop;
    END;
    /

    Blocking Session : 10
    Object (Owner/Name): SCOTT.EMP
    Object Type : TABLE
    Sessions being blocked : 11

    PL/SQL procedure successfully completed.

    HTH
    Girish Sharma
  • Rafi (Oracle DBA)
    Rafi (Oracle DBA) Member Posts: 1,305 Silver Badge
    Hi Girish,
    My stage Database does not displaying any result.I want to test this on my local database.How to create a situation where a lock should occur in scott user for emp,dept tables.


    Thanks,
    Rafi.
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown
    Accepted Answer
    Yes Rafi,

    It is working fine at my end. See below:

    Opened Session 1 with scott/tiger and:
    update emp set ename='xx' where empno=7499;

    Opened Session 2 with scott/tiger and:
    update emp set ename='xx' where empno=7499;
    <<Its lock here>> This session is locked by above one.

    Opened Session 3 with sys/pw as sysdba and:
    SQL> set serveroutput on
    SQL> BEGIN
      2  dbms_output.enable(1000000);
      3  for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
      4  b.object_name object_name, b.object_type object_type
      5  FROM v$locked_object a, dba_objects b
      6  WHERE xidsqn != 0
      7  and b.object_id = a.object_id)
      8  loop
      9  dbms_output.put_line('.');
     10  dbms_output.put_line('Blocking Session : '||do_loop.session_id);
     11  dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
     12  dbms_output.put_line('Object Type : '||do_loop.object_type);
     13  for next_loop in (select sid from v$lock
     14  where id2 = do_loop.xidsqn
     15  and sid != do_loop.session_id)
     16  LOOP
     17  dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
     18  end loop;
     19  end loop;
     20  END;
     21  /
    .
    Blocking Session : 139
    Object (Owner/Name): SCOTT.EMP
    Object Type : TABLE
    Sessions being blocked : 134
    
    PL/SQL procedure successfully completed.
    HTH
    Girish Sharma
  • HI,

    The above query will itself display the object_type and the object_name (i.e. table) and it will display the owner of it.

    Apart from what do you need to find out.

    Regards,
    Vijayaraghavan K
  • Rafi (Oracle DBA)
    Rafi (Oracle DBA) Member Posts: 1,305 Silver Badge
    Thanks Girish and Vijay.

    One last clarification Girish when I use rollback or commit than only this lock will be release or other conditions are also there?



    Thanks,
    Rafi.
This discussion has been closed.