1 2 Previous Next 15 Replies Latest reply on Jun 17, 2010 9:35 AM by rajeysh

    Blocking session

    755167
      Hi Support,

      Any SQL or completed script to check blocking session or holding the lock so can kill the session


      Regard

      WTL
        • 1. Re: Blocking session
          Kamran Agayev A.
          user8954987 wrote:
          Hi Support,

          Any SQL or completed script to check blocking session or holding the lock so can kill the session


          Regard

          WTL
          http://www.dba-oracle.com/t_tracking_oracle_blocking_sessions.htm
          • 2. Re: Blocking session
            618702
            Hi user8954987,

            There are two simple but very useful dictionary views for that;

            dba_blockers and dba_waiters. Query them and you will see the session ids for those.

            Ogan
            • 3. Re: Blocking session
              Aman....
              user8954987 wrote:
              Hi Support,
              Its not support. That's a paid option!
              Any SQL or completed script to check blocking session or holding the lock so can kill the session
              Are you on 1g or higher? If yes, then you can use EM for the same where in the Performance tab., there is a Blocking Sessions link given which can be used to diagnose the same.

              HTH
              Aman....
              • 4. Re: Blocking session
                Chinar
                user8954987 wrote:
                Hi Support,

                Any SQL or completed script to check blocking session or holding the lock so can kill the session


                Regard

                WTL
                You can select v$lock and v$locked_object views to get this information.There you will get sid(sessionID ) and kill session according this SID(alter session kill session 'sid,serial').Identify serial from v$session.
                • 5. Re: Blocking session
                  Kamran Agayev A.
                  Are you on 1g or higher?
                  1g? Wow I haven't used it :)
                  • 6. Re: Blocking session
                    rajeysh
                    Show sessions that are blocking each other

                    select     'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
                    from     v$lock l1, v$lock l2
                    where     l1.block =1 and l2.request > 0
                    and     l1.id1=l2.id1
                    and     l1.id2=l2.id2
                    /


                    Show locked objects

                    set lines 100 pages 999
                    col username      format a20
                    col sess_id      format a10
                    col object     format a25
                    col mode_held     format a10
                    select     oracle_username || ' (' || s.osuser || ')' username
                    ,     s.sid || ',' || s.serial# sess_id
                    ,     owner || '.' ||     object_name object
                    ,     object_type
                    ,     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     v$locked_object v
                    ,     dba_objects d
                    ,     v$lock l
                    ,     v$session s
                    where      v.object_id = d.object_id
                    and      v.object_id = l.id1
                    and      v.session_id = s.sid
                    order by oracle_username
                    ,     session_id
                    /

                    Show which row is locked

                    select     do.object_name
                    ,     row_wait_obj#
                    ,     row_wait_file#
                    ,     row_wait_block#
                    ,     row_wait_row#
                    ,     dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
                                        ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
                    from     v$session s
                    ,     dba_objects do
                    where     sid=&sid
                    and      s.ROW_WAIT_OBJ# = do.OBJECT_ID
                    /

                    Then select the row with that rowid...

                    select * from <table> where rowid=<rowid>;


                    LIST LOCKS

                    column lock_type format a12
                    column mode_held format a10
                    column mode_requested format a10
                    column blocking_others format a20
                    column username format a10
                    SELECT     session_id
                    ,     lock_type
                    ,     mode_held
                    ,     mode_requested
                    ,     blocking_others
                    ,     lock_id1
                    FROM     dba_lock l
                    WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread')
                    /
                    • 7. Re: Blocking session
                      755167
                      Hi All,

                      Tks for the infor. How about SQL to check the session lock the objects. Tks


                      Regard
                      William
                      • 8. Re: Blocking session
                        rajeysh
                        You can also find the information from alertlog file and trace file, who is blocking user and locking user and session id, process id, current sql statement.
                        • 9. Re: Blocking session
                          Aman....
                          Hehehe it was never meant to be out actually . So that's why they gave 10 times better, 10g ;-) .

                          Aman....
                          • 10. Re: Blocking session
                            755167
                            Hi all,


                            I have try the below script but got error. This is the error. I believe the script got bug. I using oracle 10gr2


                            ERROR at line 10:
                            ORA-00904: "MYGSETTEST": invalid identifier








                            Show which row is locked

                            select do.object_name
                            , row_wait_obj#
                            , row_wait_file#
                            , row_wait_block#
                            , row_wait_row#
                            , dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
                            ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
                            from v$session s
                            , dba_objects do
                            where sid=&sid
                            and s.ROW_WAIT_OBJ# = do.OBJECT_ID
                            /

                            Then select the row with that rowid...

                            select * from <table> where rowid=<rowid>;
                            • 11. Re: Blocking session
                              rajeysh
                              refer this link, it helps
                              http://www.orafaq.com/node/854
                              • 12. Re: Blocking session
                                755167
                                Hi,

                                Tks for the info. Can some explain the below lock expecially the session 361 and 373 as user complain their take time to save the transaction.


                                SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE BLOCKING_OTHERS LOCK_ID1
                                ---------- ------------ ---------- ---------- -------------------- ----------------------------------------
                                550 XR Null None Not Blocking 4
                                550 Control File Row-S (SS) None Not Blocking 0
                                550 RS Row-S (SS) None Not Blocking 25
                                552 PW Row-X (SX) None Not Blocking 1
                                549 Temp Segment Row-X (SX) None Not Blocking 3
                                373 DML Row-S (SS) None Not Blocking 142766
                                373 DML Row-X (SX) None Not Blocking 143035
                                373 DML Row-X (SX) None Not Blocking 142778
                                373 DML Row-X (SX) None Not Blocking 142862
                                373 DML Row-X (SX) None Not Blocking 142867
                                373 DML Row-S (SS) None Not Blocking 143083
                                373 DML Row-X (SX) None Not Blocking 142770
                                373 DML Row-X (SX) None Not Blocking 142772
                                373 DML Row-X (SX) None Not Blocking 142831
                                361 Transaction Exclusive None Not Blocking 655382
                                373 Transaction Exclusive None Not Blocking 131104
                                • 13. Re: Blocking session
                                  rajeysh
                                  Transaction Exclusive
                                  The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction.
                                  • 14. Re: Blocking session
                                    Jonathan Lewis
                                    >

                                    Rajeysh,

                                    You need to use the "code" tags to make code readable:
                                    Show sessions that are blocking each other

                                    select     'SID ' || l1.sid ||' is blocking ' || l2.sid blocking
                                    from     v$lock l1, v$lock l2
                                    where     l1.block =1 and l2.request > 0
                                    and     l1.id1=l2.id1
                                    and     l1.id2=l2.id2
                                    /
                                    The statement above needs to include the lock type in the join to avoid spurious matches.

                                    >
                                    Show locked objects
                                    from     v$locked_object v
                                    ,     dba_objects d
                                    ,     v$lock l
                                    ,     v$session s
                                    where      v.object_id = d.object_id
                                    and      v.object_id = l.id1
                                    and      v.session_id = s.sid
                                    order by oracle_username
                                    ,     session_id
                                    /
                                    The above needs to be restricted to locks of type 'TM' to avoid spurious matches.

                                    >
                                    Show which row is locked

                                    select     do.object_name
                                    ,     row_wait_obj#
                                    ,     row_wait_file#
                                    ,     row_wait_block#
                                    ,     row_wait_row#
                                    ,     dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
                                                        ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
                                    from     v$session s
                                    ,     dba_objects do
                                    where     sid=&sid
                                    and      s.ROW_WAIT_OBJ# = do.OBJECT_ID
                                    /
                                    I think you'll find that you need the data_object_id in the call to dbms_rowid.rowid_create().

                                    As a more general warning, the row_wait_XXX columns are populated by events other than data locks, and they're not always cleared down - so it's not a good idea to use a query like this to check for row locks unless you also check that the session in question is (still) waiting for a row lock (which means waiting for a TX lock in mode 6 if you're only using heap tables, but could be a TX mode 4 if you're also using IOTs).




                                    Regards
                                    Jonathan Lewis
                                    http://jonathanlewis.wordpress.com
                                    http://www.jlcomp.demon.co.uk

                                    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                                    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                                    fixed format
                                    .
                                    
                                    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                    1 2 Previous Next