Doubt related to identify main session (lock)

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Date: Wed, 20 Nov 2013 17:43:42 -0200
Message-ID: <CAJdDhaMpLQn+uxqP=MgbMD0fD1bfFUf7vcq6JWVi3fFJKJDp9A_at_mail.gmail.com>

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-l

Received on Wed Nov 20 2013 - 20:43:42 CET