oracle apps dba blog

Hello Guys,

This script is useful for finding the SID and serial number for a specific 11i/R12 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 8.1.7.4, 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;

Your Comments Most welcome.
– Vivek R