14 Replies Latest reply: Sep 10, 2011 7:08 AM by Venkat RSS

    How to check Locking sessions in oracle 10g

    Venkat
      Hi,

      I have tried to get locking sessions. i got the blocking session by using v$lock view.

      Pls help how to get locking sessions.

      Regards,
      Venkat
        • 1. Re: How to check Locking sessions in oracle 10g
          EBSDBA
          Hi,
          Please see

          Note: 200590.1 - bde_session_locks.sql - Locks for given Session ID (8.1-9.2)
          Note: 15476.1 - FAQ about Detecting and Resolving Locking Conflicts
          Note: 198150.1 - How To Indentify The Row Which is Locked By an Other User's Session
          Script: To display Locks and give the SID and Serial# of the Session to Kill). [ID 1020007.6 ]

          Thanks
          • 2. Re: How to check Locking sessions in oracle 10g
            Venkat
            Hi,

            Thanks for help. But I want to findout the locked sessions.
            • 3. Re: How to check Locking sessions in oracle 10g
              VenkatB
              >
              Thanks for help. But I want to findout the locked sessions.
              >

              What version are you on? If you are on 10g and above, V$SESSION will tell you the information straight away

              SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM V$SESISON WHERE BLOCKING_SESSION IS NOT NULL;
              • 4. Re: How to check Locking sessions in oracle 10g
                Venkat
                Hi,

                Below error came to get locking sessions.

                SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
                SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL
                *
                ERROR at line 1:
                ORA-00904: "BLOCKING_SESSION": invalid identifier
                • 5. Re: How to check Locking sessions in oracle 10g
                  EBSDBA
                  Hi,
                  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')
                  /
                  Thanks
                  • 6. Re: How to check Locking sessions in oracle 10g
                    Aman....
                    Venkat wrote:
                    I want to findout the locked sessions.
                    How about using the views dba_blockers , dba_waiters and also the script utllockt.sql ?

                    Aman....
                    • 7. Re: How to check Locking sessions in oracle 10g
                      VenkatB
                      >
                      SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
                      SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL
                      *
                      ERROR at line 1:
                      ORA-00904: "BLOCKING_SESSION": invalid identifier
                      >

                      What's your database version? Please show us all 5 digits of the version (select from v$version);*

                      In 10.1_
                      SQL> select * from v$version;
                      
                      BANNER
                      ----------------------------------------------------------------
                      Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
                      PL/SQL Release 10.1.0.4.0 - Production
                      CORE    10.1.0.4.0      Production
                      TNS for Linux: Version 10.1.0.4.0 - Production
                      NLSRTL Version 10.1.0.4.0 - Production
                      
                      SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
                      
                      no rows selected
                      In 10.2*
                      SQL> select * from v$version;
                      
                      BANNER
                      ----------------------------------------------------------------
                      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
                      PL/SQL Release 10.2.0.3.0 - Production
                      CORE    10.2.0.3.0      Production
                      TNS for HPUX: Version 10.2.0.3.0 - Production
                      NLSRTL Version 10.2.0.3.0 - Production
                      
                      SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
                      
                      no rows selected
                      In 11g _(showing a real lock situation)_
                      SQL> select * from v$version;
                      
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                      PL/SQL Release 11.2.0.1.0 - Production
                      CORE    11.2.0.1.0      Production
                      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                      NLSRTL Version 11.2.0.1.0 - Production
                      
                      5 rows selected.
                      
                      
                      SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
                      
                             SID SQL_ID        USERNAME                       BLOCKING_SESSION
                      ---------- ------------- ------------------------------ ----------------
                             145 62ttrb9xf43zr SCOTT                                      26
                      
                      1 row selected.
                      Edited by: user12035575 on Sep 10, 2011 12:42 PM
                      • 8. Re: How to check Locking sessions in oracle 10g
                        Murali
                        Below error came to get locking sessions.

                        SQL> SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
                        SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL
                        *
                        ERROR at line 1
                        ORA-00904: "BLOCKING_SESSION": invalid identifier


                        check this out???

                        it is blocking_session_status not blocking_session.. try this

                        Regards
                        Murali

                        Edited by: Murali on 10-Sep-2011 04:42
                        • 9. Re: How to check Locking sessions in oracle 10g
                          VenkatB
                          Hi Venkat

                          Or just run this independent of database version
                           select a.sid blocker, 'is blocking the session ', b.sid blockee 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;
                          You can extend this query to find the username and machine (plus all other details) from v$session using the a.sid and b.sid from above

                          To find the blocking session_
                          SELECT S.SID, S.SQL_ID, S.USERNAME, S.MACHINE from
                          V$SESSION S WHERE S.SID  = (SELECT blocker from 
                          (select a.sid blocker, 'is blocking the session ', b.sid blockee 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
                          )
                          );
                          
                          *_To find the blocked session_*
                          SELECT S.SID, S.SQL_ID, S.USERNAME, S.MACHINE from
                          V$SESSION S WHERE S.SID = (SELECT blockee from
                          (select a.sid blocker, 'is blocking the session ', b.sid blockee 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
                          )
                          );
                          Edited : Added blocker and blockee code                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                          • 10. Re: How to check Locking sessions in oracle 10g
                            Venkat
                            Hi,

                            Every one telling blocking session. Blocking session and locking and both are same?.
                            • 11. Re: How to check Locking sessions in oracle 10g
                              Venkat
                              Hi,

                              Every one telling blocking session. Blocking session and locking and both are same?.
                              • 12. Re: How to check Locking sessions in oracle 10g
                                VenkatB
                                >
                                Every one telling blocking session. Blocking session and locking and both are same?.
                                >

                                Yes, pretty much. Locking session should be blocking the other session that's interested in the same row or segment. Depending upon the situation (whether it's row level lock or table lock exclusive), the blocking session is locking session and the blocked session is locked session. It could even be on buffer cache or shared pool too (different terminology for those (b)locks.

                                Hope it's clear.
                                • 13. Re: How to check Locking sessions in oracle 10g
                                  Venkat
                                  Hi,

                                  I ran ur your query. output is..

                                  SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED BLOCKING_OTHERS
                                  ---------- -------------------------- ---------------------------------------- ---------------------------------------- -------------------
                                  2007 DML Row-X (SX) None Not Blocking
                                  2007 DML Row-X (SX) None Not Blocking
                                  2007 DML Row-X (SX) None Not Blocking
                                  2007 DML Row-X (SX) None Not Blocking


                                  How to confirm is this Session ID is locked or not?
                                  • 14. Re: How to check Locking sessions in oracle 10g
                                    Venkat
                                    Hi Every one.

                                    Solved my issue. and very clear what is blocking session locking session.

                                    Thanks for all.

                                    Venkat.