How To Kill runaway processes After Terminating Concurrent Request

Every concurrent Request uses some resources for running. If we find 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’ ) durationFROM     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 FcpvWHERE    phase_code              = ‘C’AND      status_Code             = ‘X’AND      s.paddr                 = p.addrAND      fcr.requested_by        = user_idAND      fcq.application_id      = qt.application_idAND      fcq.concurrent_queue_id = qt.concurrent_queue_idAND      userenv(’lang’)         = qt.languageAND      fcr.os_process_id       = s.processAND      fcr.Controlling_Manager = Concurrent_Process_IdAND         (                  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;

source:http://www.appsdba.info/docs/oracle_apps/performance/How_To_Kill_Concurrent_Request.pdf