The below sql retrieve the sid information for the running concurrent program.
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =&req_id
AND a.phase_code = 'R';
Also you can use the below sql to find out which sql is running for the above concurrent request id.
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.sid = &sid
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id =&req_id
AND a.phase_code = 'R';
Also you can use the below sql to find out which sql is running for the above concurrent request id.
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.sid = &sid
Good script
ReplyDeletecan say from which table we are finding the pid to sid and sid rid
ReplyDeletecan u say from which table we havi find pid to sid
ReplyDeletesid to rid
ReplyDeletehow to trouble shoot long running conncurrent jobs
ReplyDeletecan you please give me the script to find out SID of a particual application user in R12
ReplyDeletethanks a lot
ReplyDeleteit always gives wrong SID
ReplyDeleteWhy it is not showing sid after giving the request id
ReplyDeleteEnter value for req_id: 24300174
old 9: AND a.request_id =&req_id
new 9: AND a.request_id =24300174
no rows selected