The Oracle docs note that the dbms_job package
was superceded by the dbms_scheduler
package. When administering jobs to manage
system load, they suggest disabling
dbms_job. This is accomplished by
revoking the package execution priv for
users.

The problem with this dbms_job procedure
is that while we specify the
initial start time and
re-execution interval, we do not
see a mechanism for running the
job during predetermined hours
during the day. For example,
how do we use dbms_job to start a job at 8:00
AM, run it hourly, and then stop
at 5:00 PM?

The example about will use dbms_job to schedule
the job to run hourly, but to
get time intervals, it is
necessary to create two other
jobs, one to break the job at
5:00 PM and another to un-break
the job the following morning at
8:00 AM.

For advanced scheduling purposes
we can create customized
job intervals using dbms_job such that start and
stop at specified intervals.
Here are some working Oracle dbms_job scheduling frequency
examples:

The
following examples show how to
use these procedures to schedule
the my_job_proc procedure to run
immediately, then once every
hour after that:

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_job 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_job 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_job, 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

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

(

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

);

END;

/

SQL> print job

PL/SQL procedure successfully completed.

JOB

----------

21

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.

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.

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail: