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 dbms_jobs Scheduling
Oracle Tips by Burleson Consulting

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


Way back in Oracle 7, database jobs were added. Jobs were background processes run by Oracle to perform scheduled tasks. Back then, the idea was that the dbms_jobs processes were permitted via the job_queue_processes init.ora parameter and were primarily for replication purposes; namely, snapshot refreshes. Over the next few major releases, dbms_job’s usage increased to include many additional purposes, basically to the point of serving as a generic job scheduler of sorts for many different kinds of Oracle jobs. However, there are a number of shortcomings with the dbms_job facility like the fact that it cannot handle job dependencies. Thus, as of Oracle 10g, the dbms_jobs package has been superseded by the new dbms_scheduler package, which is covered in the next section, and the job_queue_processes parameter has been deprecated. In fact, Oracle recommends disabling dbms_job by revoking the package execution privilege for all users. Therefore, dbms_scheduler is truly the clear choice now.


However, for those on older versions of Oracle or who must still maintain systems built using dbms_jobs, examples of dbms_job package usage will be examined. The two most used procedures for this package are RUN and SUBMIT. Run forces a job to begin execution immediately, and submit permits one to schedule that job to run at some time in the future, with or without a next iteration repeat specification. The most challenging part, in terms of being least obvious, is specifying the next date and interval parameters, as shown here.


SQL> var job number






      job        => :job

     ,what       => 'DBMS_STATS.GATHER_SCHEMA_STATS (''BERT'');'

     ,next_date  => to_date('07/03/2008 13:49:39','mm/dd/yyyy hh24:mi:ss')

     ,interval   => 'TRUNC(LAST_DAY(SYSDATE)) + 1 + 8/24 + 30/1440'

     ,no_parse   => FALSE




SQL> print job

PL/SQL procedure successfully completed.







The next date simply had to be a valid date, but one had to remember that any time specification that was truncated (e.g. minus minutes and seconds) meant the same as all zeroes. So ‘07/03/2008’ without the 13:49:39 would actually mean midnight July 3rd. Likewise, the interval parameter was a calculation of the next date when the job would run, so it too had to be valid and was important down to the very same detailed level. Thus, next date = SYSDATE would mean run now, with an interval of SYDATE+1 meaning tomorrow at the same time as now, i.e. right now plus exactly 24 hours. If instead one wanted it run right now and then tomorrow at noon, the interval would be SYSDATE + 1 + 12/24 + 00/1440 where the 12/24 is for hours and the 00/1440 is for the minutes.


There are also three data dictionary views to check on these jobs: ALL_, DBA_ and USER_JOBS. So if the DBA wants to schedule running statistics on the BERT schema at 8:30 AM each day, here is the code to set and verify that it has been set.


SQL> select job, schema_user, last_date, next_date, interval, what from dba_jobs;



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



   1 SYSMAN       03-JUL-08 03-JUL-08 sysdate + 1 / (24 * 60)



  21 BERT                   03-JUL-08 TRUNC(LAST_DAY(SYSDATE))

                                       + 1 + 8/24 + 30/1440



Finally, if one wanted to remove a job from the schedule, simply call the REMOVE procedure like this. To see what jobs are currently running, query the dba_jobs_running data dictionary view. Remember, it only shows the jobs actually currently running, so it may not return too many rows at any given time unless a ton of stuff has been scheduled.


SQL> execute dbms_job.remove(21);


PL/SQL procedure successfully completed.


SQL> select job, schema_user, last_date, next_date, interval, what from dba_jobs;



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



   1 SYSMAN       03-JUL-08 03-JUL-08 sysdate + 1 / (24 * 60)



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.