EBS后台取消请求

set lines 200 pages 50000
select distinct 'alter system kill session ''' || sid ||','||SERIAL# || ',@' || INST_ID || ''' immediate;' from cuxsysmoniter.cux_fnd_request_running_info_v where event='Streams AQ: waiting for messages in the queue';

--1. 查询正在运行的请求

SELECT v.request_id "请求ID",
       v.requestor "请求者工号",
       p.LAST_NAME "请求者",
       v.program "请求名",
       v.program_short_name "请求简称",
       --v.REQUEST_DATE,
       v.actual_start_date "实际开始时间",
       --v.ACTUAL_COMPLETION_DATE "实际结束时间",
       (nvl(v.ACTUAL_COMPLETION_DATE,sysdate)  - v.actual_start_date) * 86400 "耗时(S)",
       v.completion_text,
       v.ARGUMENT_TEXT,
      -- v.CONTROLLING_MANAGER
      frv.RESPONSIBILITY_NAME
FROM apps.fnd_conc_req_summary_v v, apps.fnd_user s, apps.per_people_f p, apps.fnd_responsibility_vl frv
WHERE status_code = 'R'
and s.user_name=v.REQUESTOR
and sysdate between p.EFFECTIVE_START_DATE(+)  and p.EFFECTIVE_END_DATE(+)
and p.PERSON_ID(+)=s.employee_id
and frv.RESPONSIBILITY_ID=v.RESPONSIBILITY_ID;


--2. Kill正在运行的请求
--select 'alter system kill session '''||s.sid||','||s.SERIAL#|| ',@' ||s.inst_id|| ''' immediate;' SESSION_NEED_KILL

select r.request_id "Request ID",
s.sid,s.SERIAL#,s.INST_ID,p.SPID,   --kill -9 spid
g.concurrent_program_name "Concurrent Program",
'alter system kill session '''||s.sid||','||s.SERIAL#||''';' SESSION_NEED_KILL
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_programs g,
gv$session s,
gV$process p
where r.controlling_manager=p.concurrent_process_id
and q.application_id=p.queue_application_id
and q.concurrent_queue_id=p.concurrent_queue_id
and qt.application_id=q.application_id
and qt.concurrent_queue_id=q.concurrent_queue_id
and s.PADDR = p.ADDR(+)
and s.inst_id = p.inst_id(+)
and r.phase_code='R'
and qt.language in ('US')
and p.session_id=s.audsid
and g.concurrent_program_id = r.concurrent_program_id
and r.request_id in ('73772293');

--3. 取消正在运行的请求

UPDATE apps.FND_CONCURRENT_REQUESTS
   SET PHASE_CODE      = 'C',
       STATUS_CODE     = 'D',
       completion_text = 'SYSADMIN 已取消请求。'
 WHERE request_id in ('73772293');