Re: Locks
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2004 11:58:17 -0400
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@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxx]
>Sent: Friday, February 13, 2004 1:09 PM
>To: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
