Re: Locks

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Fri, 13 Feb 2004 11:58:17 -0400
Message-ID: <029d01c3f24a$3b2359a0$2501a8c0@dazasoftware.com>

Here is one example:
SELECT p.username, S.SID,S.SERIAL#,S.STATUS, s.username ora, DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL', 'RT','REDO-LOG', 'TS','TEMPORARY SEGMENT ', 'TD','TABLE LOCK', 'TM','ROW LOCK',
l2.type ) vlock, DECODE(l2.type, 'TX','DML LOCK', 'RT','REDO LOG', 'TS','TEMPORARY SEGMENT', 'TD',DECODE(l2.lmode+l2.request, 4,'PARSE ' || u.name || '.' || o.name, 6,'DDL', l2.lmode+l2.request), 'TM','DML ' || u.name || '.' || o.name, l2.type ) type, DECODE(l2.lmode+l2.request,
2,'RS', 3,'RX', 4,'S', 5,'SRX', 6,'X', l2.lmode+l2.request ) lmode, DECODE(l2.request, 0,NULL, 'WAIT' ) wait FROM v$process p, v$_lock l1,
v$lock l2, v$resource r,
sys.obj$ o, sys.user$ u,

 v$session s WHERE s.paddr = p.addr
 AND s.saddr = l1.saddr AND l1.raddr = r.addr
 AND l2.addr = l1.laddr AND l2.type <> 'MR'

AND r.id1 = o.obj# (+) AND o.owner# = u.user# (+) AND p.username LIKE '%' ORDER BY 1, 2, 3, 4, 5;

Original Message ----- From: "Naveen, Nahata (IE10)" <Naveen.Nahata_at_honeywell.com> To: <oracle-l_at_freelists.org> Sent: Friday, February 13, 2004 4:36 AM Subject: RE: Locks

Join V$LOCKED_OBJECT, V$SESSION and V$SQL

Regards
Naveen

>-----Original Message-----
>From: Arvind Kumar [mailto:arvindk_at_sqlstarintl.com]
>Sent: Friday, February 13, 2004 1:09 PM
>To: oracle-l_at_freelists.org
>Subject: Locks
>
>
>Hi all,
> how can I find the SQL statements which has caused object
>locking in
>database. I can find the
>locked object and user which has locked the object but what was the SQL
>Statement user passed to the database .
>
>
>Thanks
>
>Arvind
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 13 2004 - 09:58:17 CST