Oracle Scheduler
DBMS_SCHEDULER
DBMS_SCHEDULER
Check Job Status
Check Job Status
@/u01/dba/oraschedjobstatus.sql
@/u01/dba/oraschedjobstatusbyjobname.sql
Check Job History
Check Job History
@/u01/dba/oraschedjobhistory.sql
Check Job Definition
Check Job Definition
You can use DBMS_METADATA to extract the job definition DDL...
&MYJOBNAME and &MYSCHEMA should match the output from the Job Status script above.@/u01/dba/oraschedjobdef.sql
Enabling/Disabling Jobs
Enabling/Disabling Jobs
To prevent a job running (without removing it's definition) you should disable it. To reinstate the job schedule you should then enable it again.
Enable a job
Enable a job
EXEC dbms_scheduler.enable('&OWNER.JOB');
Stop a running job
Stop a running job
EXEC dbms_scheduler.stop_job('"&OWNER"."&JOB"');
Disable a job
NOTE: Double Quotes are necessary where the name is in mixed case.Disable a job
EXEC dbms_scheduler.disable('"&OWNER"."&JOB"');
Disable All Jobs
Disable All Jobs
@/u01/dba/orascheddisablealljobs.sql
@/tmp/tmporascheddisablealljobs.sql
Disable ALL Jobs for a specified Owner
Disable ALL Jobs for a specified Owner
@/u01/dba/orascheddisablealljobsbyowners.sql
@/tmp/tmporascheddisablealljobsbyowners.sql
Bibliography
Bibliography
https://www.opencodez.com/oracle/oracle-job-scheduler-guide-examples-part-1.htmhttps://www.opencodez.com/oracle/oracle-job-scheduler-guide-examples-part-2.htmhttps://www.opencodez.com/oracle/oracle-job-scheduler-guide-examples-part-3.htm
https://oracle-base.com/articles/10g/scheduler-10ghttps://oracle-base.com/articles/11g/scheduler-enhancements-11gr2
1422706.1 How to change DBMS_SCHEDULER credentials
23https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/ALL_SCHEDULER_JOB_RUN_DETAILS.html
12.2https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/ALL_SCHEDULER_JOB_RUN_DETAILS.html
11.2https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm (Database PL/SQL Packages and Types Reference)
12cR1https://oracle-base.com/articles/12c/dbms_credential-12cr1
10.2https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2048.htm (ALL_SCHEDULER_JOB_RUN_DETAILS)
https://oracle-base.com/articles/10g/scheduler-10ghttps://oracle-base.com/articles/11g/scheduler-enhancements-11gr2
1422706.1 How to change DBMS_SCHEDULER credentials
23https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/ALL_SCHEDULER_JOB_RUN_DETAILS.html
12.2https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/ALL_SCHEDULER_JOB_RUN_DETAILS.html
11.2https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm (Database PL/SQL Packages and Types Reference)
12cR1https://oracle-base.com/articles/12c/dbms_credential-12cr1
10.2https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2048.htm (ALL_SCHEDULER_JOB_RUN_DETAILS)