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;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips