DBMS_JOBS
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
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.
SQL> select job,
schema_user, last_date, next_date, interval,
what from dba_jobs;
JOB SCHEMA_USER
LAST_DATE NEXT_DATE INTERVAL
---- ------------
--------- --------- ------------------------
WHAT
--------------------------------------------------------------
1 SYSMAN
03-JUL-08 03-JUL-08 sysdate + 1 / (24
* 60)
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
21 BERT
03-JUL-08 TRUNC(LAST_DAY(SYSDATE))
+ 1 + 8/24 +
30/1440
DBMS_STATS.GATHER_SCHEMA_STATS ('BERT');
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;
JOB
SCHEMA_USER
LAST_DATE NEXT_DATE INTERVAL
---- ------------
--------- --------- ------------------------
WHAT
--------------------------------------------------------------
1 SYSMAN
03-JUL-08 03-JUL-08 sysdate + 1 / (24
* 60)
EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
