DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, July 16, 2018

Script to kill db session and cancel running current request

set lines 140;
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;

open trace_reqid;
loop
fetch trace_reqid into req_id,trace_sid,trace_serial;
exit when trace_reqid%notfound;
dbms_output.put_line('update apps.fnd_concurrent_requests set phase_code=''C'' , status_code=''X'' where request_id='''||req_id||''';');
end loop;
close trace_reqid;

end;
/

No comments: