Oracle Technologies Blog

By ASKM

Archive for the ‘Scripts’ Category

Killing runaway processes after terminating concurrent Request

Posted by Srikrishna Murthy Annam on October 13, 2009

Killing runaway processes after terminating concurrent Request:

Every concurrent Request uses some resources for running. If you feel that the concurrent request is taking long time and decided to terminate the concurrent request , the resources may not be released soon. These processes are called runaway processes. So we need to manually kill the processes at database and os level to have the resources released to the system.

Terminate the concurrent request from the front end. Then …
SQL>select request_id,oracle_process_id,os_process_id from fnd_concurrent_requests where request_id=’&Req_Id’;

SQL>select p.spid , s.sid , s.serial# from v$session s , v$process p where s.paddr = p.addr and s.process = &os_process_id ;

SQL> alter system kill session ‘session-id,session-serial’

$ kill -9 <server pid>

Complete details about the request can be found using the following query :

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

Posted in Applications Scripts, Oracle Apps R12 | 2 Comments »

 
%d bloggers like this: