REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM 	a_user_ses_R11.sql
REM
REM <SQLDIR_GRP>APPS_ADMIN</SQLDIR_GRP>
REM
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM
REM Purpose:
REM	<SQLDIR_TXT>
REM	Displays session information
REM	for Oracle Applications
REM	!! Will only work if AUDIT is set for USER Level !!
REM	To do this set:
REM	navigator(Sys Admin Resp)
REM	 -Profile
REM	  -System
REM	   -Find System Profile Values (FORM)
REM	   <enter:(Sign-On:Audit Level) --> search>
REM	    -System Profile Values (FORM)
REM	    <update SITE column with USER --> save>
REM	+-----------------------------------------------+
REM	| If you dont set AUDIT level to USER           |
REM	| then PACKAGE APPLSYS.FND_SIGNON will not      |
REM	| populate table FND_LOGINS with information    |
REM	| about user sessions.                          |
REM	| HERE is that package body:                    |
REM	|   UPDATE FND_LOGINS FL                        |
REM	|   SET PID = (SELECT P.PID                     |
REM	|         FROM V$PROCESS P, V$SESSION S         |
REM	|         WHERE S.AUDSID = USERENV('SESSIONID') |
REM	|         AND S.PADDR = P.ADDR)                 |
REM	+-----------------------------------------------+
REM	This script ** MUST BE RAN as SYSTEM **
REM	</SQLDIR_TXT>
REM
REM
REM Usage:
REM	a_user_ses_R11.sql
REM
REM Example:
REM	a_user_ses_R11.sql
REM
REM
REM History:
REM	MM-DD-RRRR	VMOGILEV	Created
REM
REM

--
-- grant select on any table to system
-- and create synonym for <FND_NAME>.FND_LOGINS
--                        <FND_NAME>.FND_USER
-- we are doing this since APPS user can't execute SYS.DBMS_SYSTEM package
-- you can grant execute to APPS on SYS.DBMS_SYSTEM but I wouldn't do it
--
set pages 0
set feedback off
set verify off
col fnd_name noprint new_value _owner.

select rtrim(owner)||'.'  fnd_name
from   dba_tables
where  table_name = 'FND_USER';

grant create any synonym to system with admin option;
grant select any table to system with admin option;
drop synonym system.FND_LOGINS;
drop synonym system.FND_USER;
create synonym system.FND_LOGINS for &_owner.fnd_logins;
create synonym system.FND_USER   for &_owner.fnd_user;



--
-- display all sessions within Oracle applications ONLY
--
set wrap off
set pagesize 9999
set linesize 132
set trims on
set feedback on

clear col
clear breaks

col sid_serial        format a10         heading "Sid,Serial"
col o_user            format a10         heading "-Oracle-|User Name"
col os_user           format a10         heading "-  OS  -|User Name"
col logon             format a15         heading "Login Time"
col idle              format a15         heading "Idle"
col status            format a10         heading "Status"
col apps_user         format a10         heading "- Apps -|User Name"
col terminal          format a15 trunc   heading "- Apps -|Terminal"
col lockwait          format a1          heading "L|o|c|k|w|a|i|t"

ttitle  "Current Sessions In Oracle Applications"

select  /*+ ORDERED */
           chr(39)||s.sid||','||s.serial#||chr(39)                       sid_serial
,          to_char(s.logon_time,'DDth - HH24:MI:SS')                     logon
,          floor(last_call_et/3600)||':'||
              floor(mod(last_call_et,3600)/60)||':'||
              mod(mod(last_call_et,3600),60)                             IDLE
,          s.username                                                    o_user
,          s.osuser                                                      os_user
,          s.status                                                      status
,          u.user_name                                                   apps_user
--,          n.TERMINAL_ID						 terminal
,	   DECODE(lockwait,'','','Y') lockwait
from      v$session  s
,         v$process  p
,         fnd_logins n
,         fnd_user   u
where  s.paddr      = p.addr
and    n.pid        IS NOT NULL
and    n.serial#    IS NOT NULL
and    n.serial#    = p.serial#
and    n.end_time   IS NULL
and    s.process    = n.spid -- so we don't get hung sessions with old SID and SERIAL
and    p.pid        = n.pid
and    n.user_id    = u.user_id
order by  u.user_name
,         to_char(s.logon_time,'DDth - HH24:MI:SS')
/


--
-- turn tracing ON or OFF for a given session
--
ttitle off

prompt
prompt +---------------------------------------------------------+
prompt |    The Following Portion Will Turn tracing ON or OFF    |
prompt |             To exit early press CTL-C                   |
prompt +---------------------------------------------------------+
prompt
accept _sid     number prompt "............................... Enter user SID :"
accept _serial  number prompt "............................Enter user SERIAL# :"
accept _TRUE    char   prompt "Enter TRUE to enable trace or FALSE to disable :"

execute sys.dbms_system.set_sql_trace_in_session(&_sid,&_serial,&_TRUE);