Kill Concurrent Requests ?

I have noticed today that there are lot of pending concurrent requests to our concurrent manager and is causing delay for my actual program to run.

Out of which Check Event Alert was having around 487923 approx 488K requests.

This is a Standard Program for Alerts to work properly. We cannot disable this program.

I requested my DBA to cancel them and he said he cannot do it from back end. Instead asking me to cancel from front end, 488K requests from front end ???? How many days I might take !!!!!
I decide to go on a wild way i.e. from back end. (I guess may be many people do it as regular means ).

I thought of updating the FND_CONCURRENT_REQUESTS phase_code to 'C'(Complete) and Status_code to 'D' (Canceled).
But a small catch is there here. If there is any DB session associated with this concurrent program and subsequent OS process then we need to kill both of them.

Interestingly I found that for Pending requests there are no DB session or OS process assigned. This can be seen from ORACLE_PROCESS_ID, ORACLE_SESSION_ID & OS_PROCESS_ID from FND_CONCURRENT_REQUESTS.
These columns will be only populated for requests which are in Running Phase.

ORACLE_SESSION_ID corresponds to AUDSID of V$SID and hence if you want to kill a running program from back end. kill the DB Session once you know the SID and Serial from V$session.

Summary : To kill a running requests, we need to do two tasks, update the concurrent request table & kill the db session associated.
Get Oracle_session_id from Fnd_Concurrent_Requests
Query V$session with audsid=oracle_Session_id for SID and SERIAL#
Issue Kill Statement SQL> Alter System kill Session 'SID,SERIAL#';
Update the FND_CONCURRENT_REQUESTS Table of that request id with the phase_code and status code as 'C','D' respectively.

Use this query

SELECT 'ALTER SYSTEM KILL SESSION '''||Ses.SID||','||Ses.serial#||''';'
FROM v$session ses, fnd_Concurrent_requests fcr
WHERE ses.audsid=fcr.oracle_session_id
AND request_id=any(101027686,101024278);

Have a nice day !!!