-- Find running concurrent request's database session
SELECT fcr.request_id,
ftl.user_concurrent_program_name program_name,
v.meaning phase,
vs.sid,
vs.serial#,
v1.meaning status,
fu.user_name username,
to_char(actual_start_date,'DD/MM/YYYY HH24:MI') start_date,
to_char(actual_completion_date,'DD/MM/YYYY HH24:MI') end_date,
round((fcr.actual_completion_date - fcr.actual_start_date)*24*60) minutes_run,
fcp.concurrent_program_name program_short_name,
to_number(vp.spid) process_id
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl ftl,
fnd_user fu,
v$process vp,
v$session vs,
fnd_lookup_values v,
fnd_lookup_values v1
WHERE fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fu.user_id = fcr.requested_by
AND v.meaning like '%'||nvl('Running','%')
AND v1.meaning like '%'||nvl('%','%')
AND requested_start_date <= sysdate
AND vp.spid (+) = fcr.oracle_process_id
AND vs.paddr (+) = vp.addr
AND ftl.concurrent_program_id = fcp.concurrent_program_id
AND ftl.language = 'US'
AND v.lookup_type = 'CP_PHASE_CODE'
and v.view_application_id = 0
and v.language = 'US'
and v.lookup_code = fcr.phase_code
AND v1.lookup_type = 'CP_STATUS_CODE'
and v1.view_application_id = 0
and v1.language = 'US'
and v1.lookup_code = fcr.status_code
and ((fcr.actual_start_date is null and '' is null and '' is null or fcr.actual_start_date between to_date(nvl('', to_char(sysdate-100, 'DD/MM/YYYY HH24:MI')), 'DD/MM/YYYY HH24:MI') and to_date(nvl('', to_char(sysdate, 'DD/MM/YYYY HH24:MI')), 'DD/MM/YYYY HH24:MI'))
or (fcr.actual_completion_date is null and '' is null and '' is null or fcr.actual_completion_date between to_date(nvl('', to_char(sysdate-100, 'DD/MM/YYYY HH24:MI')), 'DD/MM/YYYY HH24:MI') and to_date(nvl('', to_char(sysdate, 'DD/MM/YYYY HH24:MI')), 'DD/MM/YYYY HH24:MI')))
and (nvl(fcr.actual_completion_date,sysdate) - nvl(fcr.actual_start_date,sysdate))*24*60 >= 0
and ftl.user_concurrent_program_name like '%' || trim(replace('%','*','%')) || '%'
and to_char(fcr.request_id) like upper(replace('*','*','%'))
order by v.meaning desc, fcr.request_date desc;
-- Set max dump file size to unlimited
alter system set MAX_DUMP_FILE_SIZE=unlimited scope=memory;
-- Run trace
exec dbms_system.set_ev ('<sid>','<serial#>',10046,12,'');
-- Find trace file directory
select decode(substr(i.version,1,2), '10', v.value, v.value || '/diag/rdbms/' || lower(i.instance_name) || '/' || upper(i.instance_name)||'/trace')
from v$parameter v, v$instance i
where v.name = decode(substr(i.version,1,2), '10', 'user_dump_dest', 'diagnostic_dest');
-- Run tkprof
tkprof <trace_file.trc> <trace_file.txt> explain=apps/apps sort=exeela,fchela
Comments