Tip

Terminating 11i concurrent requests

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

    Requires Free Membership to View

||', '|| 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


This was first published in January 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.