This script is useful for finding the SID and serial number for a specific 11i concurrent request that has been terminated in the applications, but has not been cleaned up by PMON.
Sometimes in 11i, when a long-running concurrent request is terminated, it will still be active in the database for long periods taking up resources. With this script, you can find the SID, serial number and server PID for a terminated concurrent request so that the process can be killed at the database and OS level. The input parameter for this script is the terminated concurrent request ID.
After running this script you can then do an "alter system kill session" and a "kill-9" using the server pid to stop the processing faster. This script has been tested on 18.104.22.168, and on 11.5.1 through 11.5.9.
rem fndreqidx.sql rem SET LINESIZE 132 rem TTITLE 'Deleted Concurrent Manager Requests' rem COL user_concurrent_queue_name FORMAT a15 HEADING 'QUEUE NAME' trunc COL request_id FORMAT 9999999 HEADING 'REQUEST|ID' COL phase_code FORMAT a1 HEADING 'P' COL status_code FORMAT a1 HEADING 'S' COL actual_start_date FORMAT a11 HEADING 'START DATE' COL user_name FORMAT a15 HEADING 'USER|NAME' COL spid FORMAT a6 HEADING 'SERVER|PID' headsep '|' COL program FORMAT a40 HEADING 'PROGRAM' COL duration FORMAT a10 HEADING 'DURATION' COL sidserial FORMAT a11 HEADING 'SID,SERIAL#' -- SELECT qt.user_concurrent_queue_name , fcr.Request_Id Request_id , fu.User_name , p.spid , s.sid ||', '|| s.serial# SIDSERIAL , substr( Fcpv.Concurrent_Program_Name ||' - '|| Fcpv.User_Concurrent_Program_Name, 1,46) Program , to_char( fcr.actual_start_date, 'mm/dd hh24:mi' ) actual_start_date , phase_code, status_code , to_char( trunc(sysdate) + ( sysdate - fcr.actual_start_date ) , 'hh24:mi:ss' ) duration FROM apps.Fnd_Concurrent_Queues Fcq , apps.fnd_concurrent_queues_tl qt , apps.Fnd_Concurrent_Requests Fcr , apps.Fnd_Concurrent_Programs Fcp , apps.Fnd_User Fu , apps.Fnd_Concurrent_Processes Fpro , v$session s , v$process p , apps.Fnd_Concurrent_Programs_Vl Fcpv WHERE phase_code = 'C' AND status_Code = 'X' AND s.paddr = p.addr AND fcr.requested_by = user_id AND fcq.application_id = qt.application_id AND fcq.concurrent_queue_id = qt.concurrent_queue_id AND userenv('lang') = qt.language AND fcr.os_process_id = s.process AND fcr.Controlling_Manager = Concurrent_Process_Id AND (fcq.concurrent_queue_id = fpro.concurrent_queue_id AND fcq.application_id = fpro.queue_application_id ) AND (fcr.concurrent_program_id = fcp.concurrent_program_id AND fcr.program_application_id = fcp.application_id ) AND (fcr.concurrent_program_id = fcpv.concurrent_program_id AND fcr.program_application_id = fcpv.application_id ) ORDER BY fcr.actual_start_date