Re: Which tables are locked by apps(oracle financials 11.0.3) user ?

From: Daniel P. Considine <dconsid_at_ix.netcom.com>
Date: Wed, 15 Sep 1999 00:04:46 -0400
Message-ID: <37DF1ADE.C4BDDDF1@ix.netcom.com>

this works pretty well for me

set echo off;
SELECT distinct

    substr(seg.segment_name,1,4) seg

,se.sid,se.serial#
,substr(se.machine, 1, 12) mach
,pr.spid
,substr (FNDUS.user_name, 1, 15) apps_user
,substr (FR.RESPONSIBILITY_NAME, 1, 48) responsibility
,se.process remote_process,tr.start_time st_time,se.logon_time
lo_time

,ob.owner || '.' ||ob.object_name objkt,se.osuser
,se.program, se.username
,se.sql_address ,se.sql_hash_value,se.saddr
,rs.curext ,rs.curblk
,tr.addr,tr.log_io, tr.phy_io

FROM
    v$rollstat rs
,v$transaction tr
,dba_rollback_segs seg
,v$process pr
,v$session se
,v$locked_object lo
,dba_objects ob
,APPS.fnd_logins FNDL
,APPS.fnd_user FNDU
,APPS.fnd_user FNDUS
,APPLSYS.FND_LOGIN_RESPONSIBILITIES FLR
,APPLSYS.FND_RESPONSIBILITY FR

WHERE

    tr.xidusn = rs.usn
and lo.XIDUSN (+) = tr.XIDUSN
and lo.XIDSLOT (+)  = tr.XIDSLOT
and lo.XIDSQN  (+) =  tr.XIDSQN
and ob.object_id (+) = lo.object_id
and seg.segment_id = rs.usn
and se.taddr (+)   = tr.addr
and pr.addr        = se.paddr

and terminal_id ! = 'Concurrent'

AND PR.pid = FNDL.pid
AND SE.paddr = PR.addr
AND SE.process  = FNDL.spid

AND FNDUS.user_id = FNDL.user_id

AND FNDU.user_id(+) = FNDL.user_id
AND FNDU.session_number(+) = FNDL.session_number
AND FNDU.user_name is NOT null

AND FLR.login_id = FNDL.login_id
AND FR.RESPONSIBILITY_ID = FLR.RESPONSIBILITY_ID order by st_time desc;
SELECT

    substr(seg.segment_name,1,4) seg

,se.sid,se.serial#
,substr(se.machine, 1, 12) mach
,pr.spid
,'WHOKNOWS ' apps_user
,'WHOKNOWS ' responsibility
,se.process remote_process,tr.start_time st_time,se.logon_time
lo_time

,ob.owner || '.' ||ob.object_name objkt,se.osuser
,se.program, se.username
,se.sql_address ,se.sql_hash_value,se.saddr
,rs.curext ,rs.curblk
,tr.addr,tr.log_io, tr.phy_io

FROM
    v$rollstat rs
,v$transaction tr
,dba_rollback_segs seg
,v$process pr
,v$session se
,v$locked_object lo
,dba_objects ob

WHERE

    tr.xidusn = rs.usn
and lo.XIDUSN (+) = tr.XIDUSN
and lo.XIDSLOT (+)  = tr.XIDSLOT
and lo.XIDSQN  (+) =  tr.XIDSQN
and ob.object_id (+) = lo.object_id

and seg.segment_id = rs.usn

and se.taddr (+)   = tr.addr
AND SE.paddr = PR.addr
and se.machine like 'gst%'

order by st_time desc;

regards

Dan Considine Received on Tue Sep 14 1999 - 23:04:46 CDT