Can you supply script to find which sql cause lock waiting ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Can you supply script to find which sql cause lock waiting ?

  1. #1
    Join Date
    May 2002
    Posts
    34
    Hi: Can you supply script to find which sql cause lock waiting ?
    Thanks !!

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    $ORACLE_HOME/rdbms/admin/catblock.sql

  3. #3
    Join Date
    May 2002
    Location
    England
    Posts
    78

    Lock Script

    Try this....

    REM Purpose
    REM -------
    REM Display locks currently held and requested. Displays which session a
    REM blocked lock is waiting for.
    REM
    REM
    col osuser head "OS User" form a8
    col uname head "Username" form a12
    col sid head "SID" form 999
    col ltype head "Type" form a4
    col lmode head "Mode" form a10
    col blocked head "Wait" form a4
    col details head "Details" form a40

    set verify off

    accept user prompt "Username [%]: "

    select s.sid sid, s.osuser,s.logon_time, s.username uname, 'DML' ltype,
    decode (l.lmode,1,'Null',
    2,'Row-S',
    3,'Row-X',
    4,'Share',
    5,'S/Row-X',
    6,'Exclusive') lmode,
    decode (l.request,0,'No','Yes') blocked,
    u.name||'.'||o.name details
    from v$session s, v$lock l, sys.obj$ o, sys.user$ u
    where s.username like nvl(upper('&user'||'%'),'%')
    and s.sid = l.sid
    and l.id1 = o.obj#
    and l.type = 'TM'
    and o.owner# = u.user#(+)
    union all
    select s.sid sid,s.osuser,s.logon_time, s.username uname,
    decode (l.type,'TX','TX',
    'UL','USR',
    'SYS') ltype,
    decode (l.lmode,1,'Null',
    2,'Row-S',
    3,'Row-X',
    4,'Share',
    5,'S/Row-X',
    6,'Exclusive') lmode,
    decode (l.request,0,'No','Yes') blocked,
    decode (l.request,0,null,'Waiting on session '||to_char(b.sid)) details
    from v$session s, v$lock l, v$lock b
    where s.username like nvl(upper('&user'||'%'),'%')
    and s.sid = l.sid
    and l.type != 'TM'
    and l.id1 = b.id1(+)
    and b.request(+) = 0
    order by 5 desc,3 desc,2,1;
    set verify on

    REM End of file



  4. #4
    Join Date
    May 2002
    Posts
    34

    I want to find the sql cause lock waiting

    hi: Thanks your(s) reply ,but I want to get the SQL cause lock waiting,pls supply it ! thanks !!!

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    join dba_blockers and v$session

Posting Permissions

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



Click Here to Expand Forum to Full Width