13 Replies Latest reply: Feb 13, 2009 12:25 AM by oradba11 RSS

    Blocking sessions

    oradba11
      Hi ,
      I am having 9i database (9.2.0.6.0)

      I want to know ..how we can find out blocking and waiting sessions in the database whcih are holding locks on the object because of which other sessions are waiting .

      Through OEM console we can easily find out in Instance -->locks -->blocking/waiting locks

      but which view is having this informatin...?

      and how long it is locking that object in row exclusive and exclusive mode.

      i think for time we have one column ctime in the V$lock view?
        • 1. Re: Blocking sessions
          575046
          Hi,

          if you are using 9.2.0.6
          v$lock has this info

          SQL> desc v$lock;
          Name          Null?          Type
          ADDR                     RAW(8)
          KADDR                     RAW(8)
          SID                     NUMBER
          TYPE                     VARCHAR2(2)
          ID1                     NUMBER
          ID2                     NUMBER
          LMODE                     NUMBER
          REQUEST                    NUMBER
          CTIME                     NUMBER
          BLOCK                     NUMBER

          just create a query on this view v$lock and v$session.
          you can try this one.

          select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
          from v$lock l1, v$session s1, v$lock l2, v$session s2
          where s1.sid=l1.sid and s2.sid=l2.sid
          and l1.BLOCK=1 and l2.request > 0
          and l1.id1 = l2.id1
          and l2.id2 = l2.id2 ;

          i hope it can help you.

          baidba
          • 2. Re: Blocking sessions
            NitinJoshi
            Hi,

            http://www.google.co.id/search?q=locked+objects+in+oracle&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a

            plus

            select a.object_name ,c.USERNAME locking_session_name,
            DECODE(b.locked_mode,
            1, 'No Lock',
            2, 'Row Share',
            3, 'Row Exclusive',
            4, 'Shared Table',
            5, 'Shared Row Exclusive',
            6, 'Exclusive') locked_mode
            from v$locked_object b,dba_objects a,v$session c
            where a.object_id=b.object_id
            and b.session_id=c.sid

            Regards!
            • 3. Re: Blocking sessions
              connell
              or..

              select * from dba_blockers

              select * from dba_waiters

              ;)
              • 4. Re: Blocking sessions
                oradba11
                Thanks for advising but

                I think V$lock will display information about all locks whose also which are not causing blocking other sessions

                Like in

                OEM >> INSTANCE >> LOCKS >> USER TYPE LOCKS

                But i want lock causing blocking

                OEM >> INSTANCE >> LOCKS >> BLOCKING/WAITING LOCKS

                So what about qurying

                V$GLOBAL_BLOCKED_LOCKS
                • 5. Re: Blocking sessions
                  JustinCave
                  What information do you believe that DBA_BLOCKERS and DBA_WAITERS are missing?

                  Justin
                  • 6. Re: Blocking sessions
                    oradba11
                    As i wrote i am using 9i

                    so may be these views are not in 9i available only in 10g .....?

                    so for 9i we need to query V$G...... view ...
                    • 7. Re: Blocking sessions
                      JustinCave
                      Nope-- both of those views are available in 9i. [DBA_BLOCKERS|http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2209.htm#1308226] and [DBA_WAITERS|http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2414.htm#1315420] are both documented in the Oracle9i Database Reference.

                      Justin
                      • 8. Re: Blocking sessions
                        oradba11
                        Then how came i am not able to select this view ..

                        I am log in by system user.. in TOAD

                        .......it is saying OBJECT NOT FOUND
                        • 9. Re: Blocking sessions
                          Pavan Kumar
                          Hi,

                          Can you post the four digit version number. try to execute the following script
                          $ORACLE_HOME\sysman\admin\smptsixx.sql has been run

                          - Pavan Kumar N

                          Edited by: Pavan Kumar on Feb 13, 2009 11:09 AM
                          • 10. Re: Blocking sessions
                            oradba11
                            Pavan what is this four digit version no.

                            where can find this ...?
                            • 11. Re: Blocking sessions
                              Pavan Kumar
                              Hi,
                              11:47:01 zug7_real>select * from v$version;
                              
                              BANNER
                              ----------------------------------------------------------------
                              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
                              PL/SQL Release 10.2.0.4.0 - Production
                              CORE    10.2.0.4.0      Production
                              TNS for Linux: Version 10.2.0.4.0 - Production
                              NLSRTL Version 10.2.0.4.0 - Production
                              10.2.0.4.0

                              - Pavan Kumar N
                              • 12. Re: Blocking sessions
                                oradba11
                                OOO..

                                This i post already .see my first post .

                                this is 9.2.0.6.0
                                • 13. Re: Blocking sessions
                                  Pavan Kumar
                                  Hi,

                                  Have you tried to execute the script and tested ornot.

                                  - PAvan Kumar N