Finding 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 those process to release CPU memory.

1.) Take “request_id” of Concurrent Program which is currently running or which you want to cancel from database side.

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. If you are canceling a request from “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 cancelled and then it will kill database process. So ideally if you are canceling a request from “Submit Request Submission” then you should wait for some time and then check associated database process.

2.) Connect to SQLPLUS as SYSTEM User :

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