Script to kill db session of a running concurrent program

Provide the Concurrent Program name, 
set serveroutput on;
declare

trace_cmd               varchar2(200);
trace_sid               varchar2(100);
trace_serial    varchar2(100);
req_id                  varchar2(100);
sess_status     varchar2(100);
sess_osuser     varchar2(100);
sess_process    varchar2(100);
sess_spid               varchar2(100);

cursor trace_reqid is SELECT a.request_id,d.sid, d.serial#
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id in (SELECT fcr.request_id rqst_id
FROM apps.fnd_concurrent_requests fcr, apps.fnd_user fu, apps.fnd_responsibility_tl fr, apps.fnd_concurrent_programs_tl fcp
WHERE fcr.status_code LIKE ‘R’
AND fu.user_id = fcr.requested_by
AND fr.responsibility_id = fcr.responsibility_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.user_concurrent_program_name=’&a’
AND ROUND (((SYSDATE – fcr.actual_start_date) * 60 * 24), 2) > 10);

begin

open trace_reqid;
loop
fetch trace_reqid into req_id,trace_sid,trace_serial;
exit when trace_reqid%notfound;
dbms_output.put_line(‘alter system kill session ”’||trace_sid||’,’||trace_serial||”’;’);
end loop;
close trace_reqid;

end;
/

  • July 16, 2018 | 19 views
  • Comments