Search

From Concurrent to Trace

-- 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



4 views0 comments

Recent Posts

See All

Get DDL for Oracle object

set feed off set long 1000000 set longchunksize 1000000 set pages 0 set lines 32767 set trimspool on set feed off set wrap on set serveroutput on format wrapped set def off set tab off exec dbms_metad

Cancelled scheduled concurrent requests

This SQL query returns all concurrent requests that were scheduled in the past and are not scheduled currently. It can be used for identifying scheduled requests that failed to be resubmitted. select

Foreign key constraint violation

/* This PL/SQL script checks violations of foreign key constraints Example of the tables: CREATE TABLE temp_supplier ( supplier_id numeric(10) not null, supplier_name varchar2(50) not null, contact_na