Logo Background

Check Current Long Running Jobs In Oracle Applications

  • By on October 7, 2008 | 2 Comments

    You may use the script as below to detect the current long running jobs in Oracle Applications: –

    Long Running Oracle Applications Jobs

    SQL> SELECT a.request_id "Req Id"
    ,a.phase_code,a.status_code
    ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
    ,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
    FROM APPLSYS.fnd_Concurrent_requests a2,
    APPLSYS.fnd_concurrent_programs c2
    WHERE c2.concurrent_program_id = c.concurrent_program_id
    AND a2.concurrent_program_id = c2.concurrent_program_id
    AND a2.program_application_id = c2.application_id
    AND a2.phase_code || '' = 'C') avg_mins
    ,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
    FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_concurrent_programs_tl ctl
    WHERE a.controlling_manager = b.concurrent_process_id
    AND a.concurrent_program_id = c.concurrent_program_id
    AND a.program_application_id = c.application_id
    AND a.phase_code IN ('I','P','R','T')
    AND b.queue_application_id = q.application_id
    AND b.concurrent_queue_id = q.concurrent_queue_id
    AND ctl.concurrent_program_id = c.concurrent_program_id
    AND ctl.language = 'US'
    AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10
    AND (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >
    ( SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
    FROM APPLSYS.fnd_Concurrent_requests a2,
    APPLSYS.fnd_concurrent_programs c2
    WHERE c2.concurrent_program_id = c.concurrent_program_id
    AND a2.concurrent_program_id = c2.concurrent_program_id
    AND a2.program_application_id = c2.application_id
    AND a2.phase_code || '' = 'C'
    )
    ORDER BY 5 DESC;
    Previous
    Next
    » Check Completed Long Running Jobs In Oracle Applications
  1. #1 zenux
    November 5, 2008 9:18 pm

    This is a better script to check with the apps node and db node details πŸ™‚

    select a.request_id “Request Id”
    ,frt.responsibility_name
    ,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name “program”
    ,a.description
    ,a.ARGUMENT_TEXT
    ,b.node_name
    ,b.db_instance
    ,q.concurrent_queue_name
    ,a.phase_code,a.status_code, a.completion_text
    , actual_start_date
    , actual_completion_date
    , fu.user_name
    ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
    ,(select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
    from APPLSYS.fnd_Concurrent_requests a2,
    APPLSYS.fnd_concurrent_programs c2
    where c2.concurrent_program_id = c.concurrent_program_id
    and a2.concurrent_program_id = c2.concurrent_program_id
    and a2.program_application_id = c2.application_id
    and a2.phase_code || ” = ‘C’) avg_mins
    ,round((actual_completion_date – requested_start_date),2) * 24 duration_in_hours
    from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_concurrent_programs_tl ctl
    ,apps.fnd_user fu
    ,apps.FND_RESPONSIBILITY_TL frt
    where a.controlling_manager = b.concurrent_process_id
    and a.concurrent_program_id = c.concurrent_program_id
    and a.program_application_id = c.application_id
    and a.phase_code in (‘I’,’P’,’R’,’T’)
    –and a.phase_code = ‘C’
    –and a.status_code = ‘E’
    –and actual_start_date > sysdate – 30
    and b.queue_application_id = q.application_id
    and b.concurrent_queue_id = q.concurrent_queue_id
    and ctl.concurrent_program_id = c.concurrent_program_id
    and ctl.language = ‘US’
    and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 0
    and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >
    ( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
    from APPLSYS.fnd_Concurrent_requests a2,
    APPLSYS.fnd_concurrent_programs c2
    where c2.concurrent_program_id = c.concurrent_program_id
    and a2.concurrent_program_id = c2.concurrent_program_id
    and a2.program_application_id = c2.application_id
    and a2.phase_code || ” = ‘C’
    )
    and a.requested_by = fu.user_id
    and a.responsibility_id = frt.responsibility_id
    order by a.request_id

    Post ReplyPost Reply
  1. #2 zenux
    November 28, 2008 2:35 am

    This script is re-tune to add SPID on the conccurent request πŸ™‚

    select
    a.oracle_process_id “SPID”,
    a.request_id
    ,frt.responsibility_name
    ,c.concurrent_program_name || ‘: ‘ || ctl.user_concurrent_program_name
    ,a.description
    ,a.ARGUMENT_TEXT
    ,b.node_name
    ,b.db_instance
    ,q.concurrent_queue_name
    ,a.phase_code,a.status_code, a.completion_text
    , actual_start_date
    , actual_completion_date
    , fu.user_name
    ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
    ,(select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
    from APPLSYS.fnd_Concurrent_requests a2,
    APPLSYS.fnd_concurrent_programs c2
    where c2.concurrent_program_id = c.concurrent_program_id
    and a2.concurrent_program_id = c2.concurrent_program_id
    and a2.program_application_id = c2.application_id
    and a2.phase_code || ” = ‘C’) avg_mins
    ,round((actual_completion_date – requested_start_date),2) * 24 duration_in_hours
    from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_concurrent_programs_tl ctl
    ,apps.fnd_user fu
    ,apps.FND_RESPONSIBILITY_TL frt
    where a.controlling_manager = b.concurrent_process_id
    and a.concurrent_program_id = c.concurrent_program_id
    and a.program_application_id = c.application_id
    and a.phase_code in (‘I’,’P’,’R’,’T’)
    –and a.phase_code = ‘C’
    –and a.status_code = ‘E’
    –and actual_start_date > sysdate – 30
    and b.queue_application_id = q.application_id
    and b.concurrent_queue_id = q.concurrent_queue_id
    and ctl.concurrent_program_id = c.concurrent_program_id
    and ctl.language = ‘US’
    and a.requested_by = fu.user_id
    and a.responsibility_id = frt.responsibility_id
    order by a.actual_start_date desc

    Post ReplyPost Reply
Leave a Comment