c8

@f
column oracle_process_id format A8
column request_id format 9999999999
column conc_prog_name format A40
column requestor format A17

select fcr.request_id,
substr(decode (fcr.description, null, fcp.user_concurrent_program_name, fcr.description||' ('||fcp.user_concurrent_program_name||')'),1,40) conc_prog_name,
fu.user_name requestor
, sid
, vs.process
, vp.spid
from
v$session vs,
v$process vp,
apps.fnd_user fu,
apps.fnd_concurrent_programs_vl fcp,
apps.fnd_concurrent_requests fcr
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
and fcr.requested_by = fu.user_id
and fcr.oracle_process_id = vp.spid(+)
and vp.addr = vs.paddr (+)
;

frm

set echo off
set feedback off
col email_address format a35 heading 'EMAIL ADDRESS'
col user_name format a35 heading 'USER NAME'
col pid format 9999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col log_per_sec format 99999 heading 'LOG|PER|SEC'
col logical format b999999999 heading 'LOGICAL|READS'
col phy_per_sec format b9999 heading 'PHY|PER|SEC'
col physical_reads format b99999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a30 heading 'PROGRAM NAME'
col module format a12 heading 'FORM NAME'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
col last_call_min format 999 heading 'LAST|CALL|MIN'
col orcl_user format a6 heading 'ORACLE|USERID'
col status format a8 heading 'Status'
col enqueue format a8 heading 'Enqueue'

set linesize 132
set pagesize 0
set heading on
ttitle 'Long Running Active Form Sessions'
select distinct user_name,
decode( s.status, 'ACTIVE', 'ACTIVE', 'INACTIVE', 'INACTIVE', 'KILLED', 'KILLED', '?' ) status,
decode( s.lockwait, null, null, 'Enqueue' ) enqueue,
s.last_call_et/60 last_call_min,
s.module,
s.sid,
s.serial#,
-- s.username orcl_usr,
-- s.osuser osuser,
s.process,
p.spid,
to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
( i.block_gets + i.consistent_gets ) /
( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
i.block_gets + i.consistent_gets logical,
physical_reads /
( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
i.physical_reads,
-- s.action,
email_address
from applsys.fnd_logins l,
applsys.fnd_user u,
v$session s,
v$sess_io i,
v$process p
where l.user_id = u.user_id
and s.sid = i.sid
and p.pid = l.pid
-- and s.process = l.spid
and p.spid = l.process_spid(+)
and l.end_time is null
and s.paddr = p.addr(+)
and substr( s.action, 1, 4 ) = 'FRM:'
and s.last_call_et >= 300
and s.status in ( 'ACTIVE', 'KILLED' )
order by last_call_min desc
/


exit
/