This discussion is archived
14 Replies Latest reply: Sep 10, 2011 5:08 AM by Venkat RSS

How to check Locking sessions in oracle 10g

Venkat Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

    Thanks for help. But I want to findout the locked sessions.
  • 3. Re: How to check Locking sessions in oracle 10g
    VenkatB Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Every one.

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

    Thanks for all.

    Venkat.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points