Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Scheduling Conditional Job Enabling
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

The current job schedules for this example can be queried using the job_queue_query.sql script listed below.

 

job_queue_query.sql

 

set feedback off

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF';

alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';

set feedback on

 

set linesize 100

column created_timestamp format a27

column next_run_date format a34

column next_date format a20

 

prompt

prompt USER_SCHEDULER_JOBS

select

   job_name,

   enabled,

   next_run_date

from

   user_scheduler_jobs

order by

   job_name;

 

prompt USER_JOBS

select

   job,

   broken,

   next_date

from

   user_jobs

order by

   job;

 

The output of this script along with the output of the job_chain_query.sql script is listed next:

 

SQL> @job_chain_query.sql

 

no rows selected

 

SQL> @job_queue_query.sql

 

USER_SCHEDULER_JOBS

 

JOB_NAME                    ENABL NEXT_RUN_DATE

--------------------------- ----- ----------------------------

JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00

JOB_CHAIN_ENABLE_TASK_2     FALSE

JOB_CHAIN_ENABLE_TASK_3     FALSE

 

3 rows selected.

 

At this point, the first task is scheduled but has not been executed, hence no results in the job_chain table.  Rather than waiting until 6:00, it can be forced to run immediately.  The results below show that the first task has run and the second job has been enabled.

 

SQL> exec dbms_scheduler.run_job ('job_chain_enable_task_1');

 

PL/SQL procedure successfully completed.

 

SQL> @job_queue_query.sql

 

USER_SCHEDULER_JOBS

 

JOB_NAME                    ENABL NEXT_RUN_DATE

--------------------------- ----- ----------------------------

JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00

JOB_CHAIN_ENABLE_TASK_2     TRUE  08-AUG-2004 12:00:00.200000 +01:00

JOB_CHAIN_ENABLE_TASK_3     FALSE

 

 

USER_JOBS

 

no rows selected

 

SQL> @job_chain_query.sql

 

CREATED_TIMESTAMP           TASK_NAME

--------------------------- --------------------

07-AUG-2004 13:52:28.227000 TASK_1

 

Next, run the second job manually.  The results below show that the second task has run and the third job has been enabled.

 

SQL> exec dbms_scheduler.run_job ('job_chain_enable_task_2');

 

PL/SQL procedure successfully completed.

 

SQL> @job_chain_query.sql

 

CREATED_TIMESTAMP           TASK_NAME

--------------------------- --------------------

07-AUG-2004 13:52:28.227000 TASK_1

07-AUG-2004 13:59:16.666000 TASK_2

 

 

SQL> @job_queue_query.sql

 

USER_SCHEDULER_JOBS

 

JOB_NAME                   ENABL NEXT_RUN_DATE

-------------------------- ----- -----------------------------

JOB_CHAIN_ENABLE_TASK_1    TRUE  08-AUG-2004 06:00:00.800000 +01:00

JOB_CHAIN_ENABLE_TASK_2    TRUE  08-AUG-2004 12:00:00.200000 +01:00

JOB_CHAIN_ENABLE_TASK_3    TRUE  07-AUG-2004 18:00:00.700000 +01:00

 

 

USER_JOBS

 

no rows selected

 

Next, run the third job manually.  The results below show that the third task has run successfully.

 

SQL> exec dbms_scheduler.run_job ('job_chain_enable_task_3');

 

PL/SQL procedure successfully completed.

 

SQL> @job_chain_query.sql

 

CREATED_TIMESTAMP           TASK_NAME

--------------------------- --------------------

07-AUG-2004 13:52:28.227000 TASK_1

07-AUG-2004 13:59:16.666000 TASK_2

07-AUG-2004 14:02:10.948000 TASK_3

 

 

SQL> @job_queue_query.sql

 

USER_SCHEDULER_JOBS

 

JOB_NAME                    ENABL NEXT_RUN_DATE

--------------------------- ----- ----------------------------------

JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00

JOB_CHAIN_ENABLE_TASK_2     TRUE  08-AUG-2004 12:00:00.200000 +01:00

JOB_CHAIN_ENABLE_TASK_3     TRUE  07-AUG-2004 18:00:00.700000 +01:00

 

 

USER_JOBS

 

no rows selected

 

Finally, run the first job again to see that the subsequent jobs have been enabled or disabled appropriately.

 

SQL> exec dbms_scheduler.run_job ('job_chain_enable_task_1');

 

PL/SQL procedure successfully completed.

 

SQL> @job_chain_query.sql

 

CREATED_TIMESTAMP           TASK_NAME

--------------------------- --------------------

07-AUG-2004 14:03:55.683000 TASK_1

 

 

SQL> @job_queue_query.sql

 

USER_SCHEDULER_JOBS

 

JOB_NAME                    ENABL NEXT_RUN_DATE

--------------------------- ----- ----------------------------

JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00

JOB_CHAIN_ENABLE_TASK_2     TRUE  08-AUG-2004 12:00:00.700000 +01:00

JOB_CHAIN_ENABLE_TASK_3     FALSE 07-AUG-2004 18:00:00.700000 +01:00

 

 

USER_JOBS

 

no rows selected

 

Care must be taken when running the pre-10g version of this code due to the way the broken procedure works.  When a job has its broken flag set to FALSE, its next run date is set to the value specified by the next_date parameter.  If this is not specified, it defaults to the current datetime.  As a result, the enabled job will not run at the expected time.  In this example, the next_date parameter has been specified as a two-minute interval for the convenience of testing, but in a real example, it must be set to an appropriate datetime value.


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.