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

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 that 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.