Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Tuesday, May 12, 2009

Check what a session does on OS level

First find the oracle session id of the concurrent program with this sql:

SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from apps.fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
and request_id=&request_ID

Get the OS Process id by passing the session id obtained from above query to the query below:

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid = &sid;

The above query will give you the os process id in the column spid. Once you get the os process id, go to unix prompt

truss -p os_process_id

No comments: