Sunday, April 14, 2019

Query to fetch Database Jobs details ALL_SCHEDULER_JOBS, ALL_SCHEDULER_RUNNING_JOBS & ALL_SCHEDULER_JOB_RUN_DETAILS













In this post, we have given you the useful query which will fetch you the list of scheduled database jobs with details.

Query (Scheduled Jobs)

SELECT job_name,
       comments,
       job_type,
       job_action,
       TO_CHAR(start_date) start_date,
       repeat_interval,
       TO_CHAR(end_date) end_date,
       auto_drop,
       restartable,
       state,
       run_count,
       max_runs,
       failure_count,
       TO_CHAR(last_start_date) last_start_date,
       TO_CHAR(last_run_duration) last_run_duration,
       TO_CHAR(next_run_date) next_run_date,
       TO_CHAR(max_run_duration) max_run_duration
  FROM all_scheduler_jobs


Query (Completed Jobs)

SELECT log_id,
       TO_CHAR(log_date) log_date,
       owner,
       job_name,
       job_subname,
       status,
       error#,
       TO_CHAR(req_start_date) req_start_date,
       TO_CHAR(actual_start_date) actual_start_date,
       TO_DATE(TO_CHAR(actual_start_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') from_act_start_dt,
       TO_DATE(TO_CHAR(actual_start_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') to_act_start_dt,
       TO_CHAR(run_duration) run_duration,
       --to_char(sysdate-run_duration) run_duration,
       instance_id,
       session_id,
       slave_pid,
       TO_CHAR(cpu_used) cpu_used,
       credential_owner,
       credential_name,
       destination_owner,
       destination,
       additional_info
  FROM all_scheduler_job_run_details


Query (Currently Running Jobs)

SELECT log_id,
       NULL log_date,
       owner,
       job_name,
       job_subname,
       'RUNNING' status,
       NULL error#,
       NULL req_start_date,
       TO_CHAR((SYSDATE - elapsed_time), 'DD/MM/YYYY HH24:MI:SS') actual_start_date,
       TO_DATE(to_char(SYSDATE - elapsed_time, 'DD-MON-YYYY'),
               'DD-MON-YYYY') from_act_start_dt,
       TO_DATE(TO_CHAR(SYSDATE - elapsed_time, 'DD-MON-YYYY'),
               'DD-MON-YYYY') to_act_start_dt,
       TO_CHAR(elapsed_time) run_duration,
       running_instance instance_id,
       TO_CHAR(session_id) session_id,
       TO_CHAR(slave_process_id) slave_pid,
       TO_CHAR(cpu_used) cpu_used,
       credential_owner,
       credential_name,
       destination_owner,
       destination,
       '' additional_info
  FROM all_scheduler_running_jobs
 Hope it Helps!!

0 Responses to “Query to fetch Database Jobs details ALL_SCHEDULER_JOBS, ALL_SCHEDULER_RUNNING_JOBS & ALL_SCHEDULER_JOB_RUN_DETAILS”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search