-
Hi: Can you supply script to find which sql cause lock waiting ?
Thanks !!
-
$ORACLE_HOME/rdbms/admin/catblock.sql
-
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
-
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 !!!
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|