Re: Doubt related to identify main session (lock)
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: "ecandrietta@xxxxxxxxx" <ecandrietta@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Nov 2013 11:46:32 -0800 (PST)
set hea on pages 99 lines 200 space 1
col object form a40
col blocker form a10
col waiter form a10
col blocker_mins form 999.99
col waiter_mins form 999.99
col blocker_info form a37
col waiter_info form a37
select v1.sid||' ['||v1.inst_id||']' blocker, v2.sid||' ['||v2.inst_id||']'
waiter,
v1.ctime/60 blocker_mins, v2.ctime/60 waiter_mins,
s1.module||' - '||s1.action blocker_info, s2.module||' - '||s2.action
waiter_info,
o.owner||'.'||o.object_name object
from
(select sid, id1, id2, block, request, type, ctime, inst_id
from gv$lock
where block>=1
and ctime > 300) v1, -- 300 = 5 mins. change this parameter to check
for just long-held locks
(select sid, id1, id2, block, request, type, ctime, inst_id
from gv$lock
where request > 0) v2,
(select sid, id1, id2, block, request, type, inst_id
from gv$lock
where type = 'TM') v3,
dba_objects o, gv$session s1, gv$session s2
where v1.id1 = v2.id1
and v1.id2 = v2.id2
and v1.sid = v2.sid
and v1.inst_id = v3.inst_id
and o.object_id = v3.id1
and s1.inst_id = v1.inst_id and s1.sid = v1.sid
and s2.inst_id = v2.inst_id and s2.sid = v2.sid
order by 1,2
/
David Fitzjarrell
On Wednesday, November 20, 2013 12:44 PM, Eriovaldo Andrietta
<ecandrietta@xxxxxxxxx> wrote:
Hello,
I have several locks in the instance.
All stopped and don´t finish ...
I would like to know what is the main session that is the root cause for all
locks.
I used these queries, but I cannot identify the session that causes the first
lock.
Select distinct s.sid,
s.serial#,
p.spid "O.S|Id",
t.piece,
t.sql_text
from dba_objects o ,
v$locked_object l,
v$session s,
v$process p,
v$sqltext t
where l.object_id=o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
and t.address = s.sql_address
and t.hash_value = s.sql_hash_value
order by sid,serial#;
SELECT session_id, LPAD(' ',DECODE(l.xidusn,0,3,0))||l.oracle_username
"UserName",
o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER by o.object_id, 1 desc;
Does anyone have query for identify it ?
Regards
Eriovaldo
Other related posts:
