How to Find Database Session (SID) & Process Associated with a Concurrent Program Which is Currently Running

Concurrent Processing - How to Find Database Session & Process Associated with a Concurrent Program Which is Currently Running

Goal

1. How to find database session and process associated with a concurrent program which is currently running.

2. How to cancel a concurrent request from database side.

Solution

A concurrent program can be canceled either from "Submit Request Submission" form or from database side also. In case of custom concurrent programs, sometimes concurrent program do not release database session and process even though it has canceled from "Submit Request Submission" form. Active database process can be seen in running status. In those cases there is a need to manually kill those process to release CPU memory.

1. Take the "request_id" of a Concurrent Program which is currently running or which is to be canceled from the database side.

2. Connect to SQLPLUS as APPS User :

SQL> SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = &request_id);

Note : oracle_process_id is Unix PID and request_id is running concurrent program's request ID. If "sid" and "serial#" value is returning then it means that process is running at database level. When canceling a request from the "Submit Request Submission" form, then it should release associated database process and session but it doesn't mean that it will kill database process immediately. Database process will take their own time to validate concurrent program execution process that has been canceled and then it will kill database process. So ideally when canceling a request from "Submit Request Submission", wait for some time and then check associated database process.

3. Connect to SQLPLUS as the SYSTEM user:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Note the "sid" and "serial#" value returned from step 1.