1 2 Previous Next 19 Replies Latest reply on Feb 22, 2010 12:00 PM by Aman....

    How to see lock on table and query?

    Rafi (Oracle DBA)
      Hi All,
      How do we see lock on table and query?


      Thanks,
      Rafi
        • 1. Re: How to see lock on table and query?
          Girish Sharma
          Rafi,
          Script to find locks in the database
          http://www.dbapool.com/dbscripts/script_33.html

          HTH
          Girish Sharma
          • 2. Re: How to see lock on table and query?
            544167
            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
            • 3. Re: How to see lock on table and query?
              753092
              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
              • 4. Re: How to see lock on table and query?
                Vijayaraghavan Krishnan
                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
                1 person found this helpful
                • 5. Re: How to see lock on table and query?
                  Rafi (Oracle DBA)
                  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.
                  • 6. Re: How to see lock on table and query?
                    Girish Sharma
                    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
                    • 7. Re: How to see lock on table and query?
                      Rafi (Oracle DBA)
                      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.
                      • 8. Re: How to see lock on table and query?
                        Girish Sharma
                        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
                        • 9. Re: How to see lock on table and query?
                          Vijayaraghavan Krishnan
                          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
                          • 10. Re: How to see lock on table and query?
                            Rafi (Oracle DBA)
                            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.
                            • 11. Re: How to see lock on table and query?
                              Girish Sharma
                              When we end the transaction; and transaction ends with either commit or rollback.

                              Regards
                              Girish Sharma
                              • 12. Re: How to see lock on table and query?
                                Rafi (Oracle DBA)
                                Thanks very much girish.That means no other way to release lock except either rolling back or commiting.
                                I just confirmed one more thing lock should be release from the same session(blocking session) that is holding the lock is it right?

                                With regards,
                                Rafi.
                                • 13. Re: How to see lock on table and query?
                                  Girish Sharma
                                  Yes, we can end transaction (using either commit or rollback) from blocking session or by killing session by alter system kill session command. Transaction will be end by only those session who have acquired the lock; i mean only that session is having key to unlock it or if it is instance recovery case then it will done by PMON.

                                  Regards
                                  Girish Sharma

                                  Edited by: Girish Sharma on Feb 22, 2010 4:48 PM
                                  Type correctred
                                  • 14. Re: How to see lock on table and query?
                                    Rafi (Oracle DBA)
                                    Hi Girish ,
                                    You mean alter system kill session(sid,serial no);
                                    This can be used by only sys or system user or any other user also having alter system privilege?

                                    Thanks,
                                    Rafi.
                                    1 2 Previous Next