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_wsesd.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	Allows to get details for an NCA session by OS PID.  
REM	Use for R11i connections to trace back to the middle tier.
REM	This script can report session by
REM	either backend PID or front end PID.
REM     </SQLDIR_TXT>
REM
REM Usage:
REM     a_wsesd.sql
REM
REM Example:
REM     a_wsesd.sql
REM
REM
REM History:
REM     11-04-2001      VMOGILEV        Created
REM
REM

set lines 132

alter session set nls_date_format='MON-DD HH24:MI';

prompt This script can report session by
prompt either backend PID or front end PID
prompt Please make your selection below:
prompt +--------------------------------+

accept middle_tier_name          prompt "Enter Middle Tier Name: "
accept middle_tier_forms_osowner prompt "Enter osuser that starts FORMS server [applmgr]: "


col backend_pid format 9999999 heading "O-pid"
col sid_serial format a10
col username format a7 heading "O-User"
col client_detail format a23 heading "Client Detail:|machine.osuser.pid"
col module format a8
col action format a25 trunc

select p.spid backend_pid, SUBSTR(s.sid||','||s.serial#,1,15) sid_serial
,      s.username, s.machine||'.'||s.osuser||'.'||s.process client_detail
,      module, action, logon_time
from v$session s, v$process p 
where s.machine='&&middle_tier_name'
and s.osuser=NVL('&&middle_tier_forms_osowner','applmgr')
and s.process is not null
and s.paddr = p.addr
and DECODE('&&backend_pid',NULL, 'x', p.spid) = DECODE('&&backend_pid', NULL, 'x', '&&backend_pid')
and DECODE('&&front_end_pid', NULL, 'x', to_char(s.process)) = DECODE('&&front_end_pid', NULL, 'x', '&front_end_pid')
/

undefine backend_pid
undefine front_end_pid