Re: Doubt related to identify main session (lock)
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 20 Nov 2013 11:46:32 -0800 (PST)
Message-ID: <1384976792.62991.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>
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_at_gmail.com> 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
Date: Wed, 20 Nov 2013 11:46:32 -0800 (PST)
Message-ID: <1384976792.62991.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>
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_at_gmail.com> 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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 20 2013 - 20:46:32 CET