long running form session

Long Running Active Form Sessions

--------------------------------------------

rem  db0008_rac.sql

rem

set linesize 132

set pagesize 60

set tab off

rem

ttitle 'Long Running Active Form Sessions'

rem

col email_address format a26 heading 'EMAIL ADDRESS'

col user_name format a10 heading 'USER NAME'

col pid format 9999 heading 'PID'

col spid format a6 heading 'SERVER|PID'

col inst_id format 9 heading 'I'

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 a1 heading 'S'

col enqueue format a1 heading 'E'

rem

select distinct user_name, 

       decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,

       decode( s.lockwait, null, null, 'E' ) enqueue,

       s.last_call_et/60 last_call_min,

       s.module,

       s.inst_id,

       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,

       gv$session s,

       gv$sess_io i,

       gv$process p

 where l.user_id = u.user_id

   and s.sid = i.sid

   and s.inst_id = i.inst_id

   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 s.inst_id = p.inst_id(+)

   and ( s.module in ( 'FNDATTCH', 'FNDSCSGN' )

      or substr( s.action, 1, 5 ) = 'FRM::' )

   and s.last_call_et >= 300

   and s.status in ( 'ACTIVE', 'KILLED' )

 order by last_call_min desc;

rem

set linesize 80

set pagesize 24

set tab on

---------------------------------------------