Re: Doubt related to identify main session (lock)

Tks Rodrigo and David...
I will try both ...

Regards.
Eriovaldo

On Wed, Nov 20, 2013 at 6:20 PM, Rodrigo Mufalani <rodrigo_at_mufalani.com.br>wrote:

> Hi,
>
> Query v$wait_chains on 11g+ migth help.
>
> All the best,
>
> Mufalani
>
>
> Enviado por Samsung Mobile
>
>
>
> -------- Mensagem original --------
> De : Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> Data:
> Para: ORACLE-L <oracle-l_at_freelists.org>
> Assunto: Doubt related to identify main session (lock)
>
>
> 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