To check The Locks
===================
set feedback on
set lines 132
column sess format a8 word_wrapped
column id1 format 99999999
column id2 format 99999999
column req format 999
column type format a4
column event format a15 word_wrapped
column seconds_in_wait format 999999
column status format a10
column "Module" format a30 word_Wrapped
SELECT lpad('-->',DECODE(vl.request,0,0,5),' ')||vl.sid sess
, vl.id1
, vl.id2
, vl.lmode
, vl.request req, vl.type,
sw.event event, sw.seconds_in_wait, s.status
FROM V$LOCK vl, v$session_wait sw, v$session s
WHERE vl.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
and vl.sid = sw.sid
and vl.sid = s.sid
ORDER BY id1,request
/
or
set serveroutput on size 10000
declare
cursor c1 is
select * from v$lock where request != 0
order by id1, id2;
wid1 number := -999999;
wid2 number := -999999;
wholder_detail varchar2(200);
v_err_msg varchar2(80);
wsid number(5);
wstep number(2);
wtype varchar2(10);
wobject_name varchar2(180);
wobject_name1 varchar2(80);
wlock_type varchar2(50);
w_lastcallet varchar2(11);
h_lastcallet varchar2(11);
-- Added by Harish, 24-Feb-2010
cursor c2 (v_id1 v$lock.ID1%TYPE,v_id2 v$lock.ID2%TYPE) is select sid , type from v$lock
where id1 = v_id1
and id2 = v_id2
and request = 0
and lmode != 4;
begin
for c1_rec in c1 loop
if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
null;
else
wstep := 10;
/*
select sid , type into wsid , wtype
from v$lock
where id1 = c1_rec.id1
and id2 = c1_rec.id2
and request = 0
and lmode != 4;
*/
for c2_rec in c2(c1_rec.id1,c1_rec.id2) loop
wsid:=c2_rec.sid;
wtype:=c2_rec.type;
dbms_output.put_line(' ');
wstep := 20;
select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= wsid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
begin
select decode(wtype,'TX', 'Transaction',
'DL', 'DDL Lock',
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalida-tion',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
'Un-Known Type of Lock')
into wlock_type
from dual;
declare
cursor c3 is
select object_id from v$locked_object
where session_id = wsid;
begin
wobject_name := '';
for c3_rec in c3 loop
select object_type||': '||owner||'.'||object_name
into wobject_name
from dba_objects
where object_id = c3_rec.object_id;
wobject_name := wobject_name ||' '||wobject_name1;
end loop;
exception
when others then
wobject_name := wobject_name ||' No Object Found';
end;
dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
exception
when no_data_found then
dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
end;
end loop; -- Added by Harish
end if;
wstep := 30;
select '.... Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process ||
' SID:' || s.sid || ' Status: ' || s.status ||
' (' || floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) ||
') Module:'|| module ||
' AppSrvr: ' || substr(replace(machine,'GEIPS-AMER\',null),1,15)
into wholder_detail
from v$session s, v$process p
where s.sid= c1_rec.sid
and s.paddr = p.addr;
dbms_output.put_line(wholder_detail);
wid1 := c1_rec.id1;
wid2 := c1_rec.id2;
end loop;
if wid1 = -999999 then
wstep := 40;
dbms_output.put_line('No one requesting locks held by others');
end if;
exception
when others then
v_err_msg := (sqlerrm ||' '|| sqlcode||' step='||to_char(wstep));
DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/
or
set pagesize 1000
set line 132
break on "SID"
col Object format a35 wrap
col Locker format a10
col Type format a10
col Program format a30 wrap
col Lockmode format a15 wrap
col Wait format 9999999
col Status format a8
SELECT
SESSION_ID "SID",
OWNER||'.'||OBJECT_NAME "Object",
ORACLE_USERNAME "Locker",
-- nvl(lockwait,mysessf(lockwait)) "Wait",
lockwait "Wait",
DECODE(LOCKED_MODE,
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', 'UNKNOWN') "Lockmode",
OBJECT_TYPE "Type",
c.ROW_WAIT_ROW#,
c.Status "Status"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/
No comments:
Post a Comment