Script to tie all Oracle sessions to their Apps login name and also tells  if Remote login or Character, plus system
process id, plus IO activity.

rem   orausers.sqlrem
REM ***************************************************************************
REM *  This SQL script displays the oracle connections and perf stats
REM ***************************************************************************
REM *
REM *
REM * History:
REM *    24-JUL-96     C.Vicha          Created script.
REM *    03-MAR-97   C.Vicha          Fixed multi fnd login lines.
REM *    20-MAY-97   C.Vicha          Redo how fnd login is used.
REM ***************************************************************************
set term off;
drop table sys_actv_temp_table;
create table sys_actv_temp_table as
 SELECT  s.sid,
  decode(s.osuser,'OraUser','*',null) thru_sqlnet,
         s.username ora_uid,
  s.osuser usr,
   ' ' apps,
  p.spid,
         io.block_gets + io.consistent_gets logical_reads,
         io.physical_reads,
         io.block_changes + io.consistent_changes logical_writes
   FROM  v$sess_io io,
  v$process p,
  v$session s
   WHERE s.paddr = p.addr
          AND s.sid = io.sid;

 update sys_actv_temp_table sa
    set (usr, apps) = ( SELECT  c.user_name, '*'
                     FROM  applsys.fnd_user c,
                           applsys.fnd_logins b,
                           v$session s2
                     WHERE s2.sid = sa.sid
                       AND s2.process = b.spid
                       AND b.end_time is null
                       AND (sysdate - b.start_time) <= 1
                       AND to_char(b.start_time,'YYMMDDHH24MISS') =
                     (select max(to_char(b2.start_time,'YYMMDDHH24MISS'))
                           from applsys.fnd_logins b2
                           where b.spid = b2.spid
                            and b2.end_time is null)
                       AND b.user_id = c.user_id)
 where exists ( select 'X'
                   FROM  applsys.fnd_user c,
                         applsys.fnd_logins b,
    v$session s2
                   WHERE s2.sid = sa.sid
                     AND s2.process = b.spid
                     AND b.end_time is null
                     AND (sysdate - b.start_time) <= 1
                     AND to_char(b.start_time,'YYMMDDHH24MISS') =
                   (select max(to_char(b2.start_time,'YYMMDDHH24MISS'))
                         from applsys.fnd_logins b2
                         where b.spid = b2.spid
                          and b2.end_time is null)
               AND b.user_id = c.user_id);
set term on;
set pause on;
set pagesize 22;
ttitle 'User Session I/O'
col sid format 9999
col thru_sqlnet format a1 heading 'N'
col ora_uid format a15 heading 'ORA_UID'
col usr format a10 heading 'User'
col apps format a1 heading 'A'
col spid format 99999999 heading 'SPID'
col logical_reads format 999999999 heading 'LOGICAL|READS'
col physical_reads format 99999999 heading 'PHYSICAL|READS'
col logical_writes format 99999999 heading 'LOGICAL|WRITES'
select sid, thru_sqlnet, ora_uid, usr, apps, spid,
       logical_reads, physical_reads, logical_writes
from sys_actv_temp_table
ORDER BY 3, 4;
set term off;
set pause off;
drop table sys_actv_temp_table;
set term on;
set pause on;

Return to : Oracle Database, SQL, Application, Programming Tips