Sunday, April 14, 2019

Oracle Scheduler - DBMS_SCHEDULER (Database Jobs) – Example Scripts – (Alternate concurrent manager)













In this post, we have introduced a powerful utility provided by Oracle named as DATABASE JOBS. This can be utilized to schedule the execution of our custom packages, shell scripts in a predefined interval.

From oracle apps perspective, this can be used as a second concurrent manager. We have personally used the same for triggering emails, updating staging tables in regular interval. It’s so robust and perfect. 

Example Scripts:


Action
API
Sample Script
Create Job
DBMS_SCHEDULER.create_job
Disable Job
DBMS_SCHEDULER.disable_job
Enable Job
DBMS_SCHEDULER.enable_job
Stop Job
DBMS_SCHEDULER.stop_job
Drop Job
DBMS_SCHEDULER.drop_job
Queries
ALL_SCHEDULER_JOB_RUN_DETAILS
ALL_SCHEDULER_RUNNING_JOBS
ALL_SCHEDULER_JOBS

Hope it helps!!

Sunday, April 14, 2019 by Team search · 0

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

by Team search · 0

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.