Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2001
    Posts
    80

    Unanswered: How to monitor the locking

    I am new in Oracle, when a user is deleting some rows in a table, he reported the session hang in there and doesn't respond, other users behaviours normal, I suspect that user is being locked by another user, how can I know he is locked by who and what kind of locks is employed?

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I run these queries to determine locking.
    Start with the first, goto the second.
    If you get a BLOCKING then use the third.
    I believe I got them from Tom Kyte:
    PHP Code:
    SELECT SUBSTR(TO_CHAR(session_id),1,5"SID",
           
    SUBSTR(lock_type,1,15"Lock Type",
           
    SUBSTR(mode_held,1,15"Mode Held",
           
    SUBSTR(blocking_others,1,15"Blocking?"
      
    FROM dba_locks
    /


    select substr(to_char(session_id),1,5"sid",
           
    substr(lock_type,1,15"lock type",
           
    substr(mode_held,1,15"mode held",
           
    substr(blocking_others,1,15"blocking?"
      
    from dba_locks
      where blocking_others 
    'Blocking'
    /

    col blocker format a15
    col blockee format a15
    select
          
    (select username from v$session where sid=a.sidblocker,
           
    a.sid' is blocking ',
           (
    select username from v$session where sid=b.sidblockee,
               
    b.sid
      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

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2006
    Posts
    59
    u can use pl/sql developer to check for locking.. thats easy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •