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_ses_by_pid.sql
REM
REM <SQLDIR_GRP>APPS_CONC_MAN APPS_ADMIN MOST</SQLDIR_GRP>
REM
REM SQLDIR Group Descriptions:
REM	APPS		-	APPS General
REM	APPS_INST	-	APPS Installation
REM	APPS_CONC_PROG	-	APPS Concurrent Programs
REM	APPS_CONC_MAN	-	APPS Concurrent Managers
REM	APPS_ADMIN	-	APPS Administration
REM	DBF		- 	Data Files
REM	TABSP		- 	Tablespace
REM	UTIL		- 	Utility
REM	INDX		- 	Index
REM	LOG		- 	Redo Log
REM	RBS		- 	Rollback
REM	MAINT		- 	Maintenance
REM	REVERSE		- 	Reverse Engineering
REM	SGA		- 	SGA Maintenance
REM	TAB		- 	Table
REM	USER		- 	User Management
REM	STATS		- 	Statistics
REM	STORAGE		- 	Storage Management
REM	INIT		- 	Database Init Parameters
REM	LATCH		- 	Latches
REM	LOCK		- 	Locks
REM	SEGMENT		- 	Segment Management
REM	BACKUP		- 	Backup Management
REM	PQ		- 	Parallel Query
REM	TRACE		- 	SQL Tracing Tuning
REM	PART		- 	Partitioning
REM	MOST		- 	Favorite Scripts
REM 
REM Author:
REM	Vitaliy Mogilevskiy
REM
REM 
REM Purpose:
REM     <SQLDIR_TXT>
REM	** SHOULD BE RUN AS APPS **
REM	Reports the following:
REM	----------------------
REM	- session detail from v$session, v$process
REM	   by PID (from backend). 
REM	- any running requests from apps.FND_CONCURRENT_REQUESTS
REM	   output includes REQ_ID, Phase, Status, O-PID, A-PID, OUT File
REM	- calls analyzereq.sql to print details of a REQ_ID
REM	NOTE:
REM	   to link request id to a session use one of the following
REM	      o fnd_concurrent_requests.oracle_session_id = v$session.audsid
REM	      o fnd_concurrent_requests.oracle_process_id = v$process.spid 
REM	        (v$process.addr = v$session.paddr)
REM	      o fnd_concurrent_requests.os_process_id     = v$session.process
REM     </SQLDIR_TXT>
REM
REM Usage:
REM     a_ses_by_pid.sql
REM 
REM Example:
REM     a_ses_by_pid.sql
REM
REM
REM History:
REM	12-18-2001	Vitaliy Mogilevskiy	Created
REM	10-16-2002	VMOGILEV		added machine and oracle_session_id
REM
REM

undefine pid

clear col
set head on
set pages 60

set lines 200
col sid format 9999
col serial# format 999999
col username format a9
col osuser format a10
col machine format a12 trunc
col maction format a42 trunc

col oracle_process_id format a7      heading "O-pid"
col os_process_id     format a7      heading "A-pid"
col oracle_session_id format 9999999999 heading "O-audsid"
col outfile_name      format a70     heading "Out File" 

select s.sid,s.serial#,s.audsid, s.username,
       s.status,s.osuser,s.process,s.machine,
	   p.spid, s.module||' '||s.action maction
from v$session s
,    v$process p 
where (s.process='&&pid' OR
       s.paddr = (select addr from v$process where spid='&&pid'))
and   s.paddr = p.addr;


select request_id, phase_code, status_code,
       oracle_process_id, os_process_id, oracle_session_id,
	   outfile_name
from apps.FND_CONCURRENT_REQUESTS
where phase_code<>'C'
and phase_code<>'P'
/

undefine pid

--@analyzereq.sql