|
As a review, the dbms_job.submit
procedure accepts three
parameters, the name of the job
to submit, the start time for
the job, and the interval to
execute the job:
dbms_job.submit(
what=>'statspack_alert.sql;',
next_date=>sysdate+1/24,
-- start next hour
interval=>'sysdate+1/24');
-- Run every hour
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:
BEGIN
DBMS_JOB.isubmit (
job => 99,
what => 'my_job_proc(''DBMS_JOB.ISUBMIT
Example.'');',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24 /* 1
Hour */');
COMMIT;
END;
/
--
-- Schedule
a snapshot to be run on this
instance every hour
variable
jobno number;
variable
instno number;
begin
select
instance_number into :instno
from v$instance;
--
------------------------------------------------------------
-- Submit job
to begin at 0600 and run
every hour
--
------------------------------------------------------------
dbms_job.submit(
:jobno, 'BEGIN
statspack_alert_proc; END;',
trunc(sysdate)+6/24,
'trunc(SYSDATE+1/24,''HH'')',
TRUE,
:instno);
--
------------------------------------------------------------
-- Submit job
to begin at 0900 and run 12
hours later
--
------------------------------------------------------------
dbms_job.submit(
:jobno,
'BEGIN statspack_alert_proc;
END;',
trunc(sysdate+1)+9/24,
'trunc(SYSDATE+12/24,''HH'')',
TRUE,
:instno);
--
------------------------------------------------------------
-- Submit job to begin at
0600 and run every 10
minutes
--
------------------------------------------------------------
dbms_job.submit(
:jobno,
'BEGIN statspack_alert_proc;
END;',
trunc(sysdate+1)+6/24,
'trunc(sysdate+1/144,''MI'')',
TRUE,
:instno);
--
----------------------------------------------------------
-- Submit job
to begin at 0600 and run
every hour, Monday - Friday
--
---------------------------------------------------------
dbms_job.submit(
:jobno,
'BEGIN statspack_alert_proc;
END;',
trunc(sysdate+1)+6/24,
trunc(
least(
next_day(SYSDATE - 1,'MONDAY'),
next_day(SYSDATE - 1,'TUESDAY'),
next_day(SYSDATE - 1,'WEDNESDAY'),
next_day(SYSDATE - 1,'THURSDAY'),
next_day(SYSDATE - 1,'FRIDAY')
)
+1/24,'HH')',
TRUE,
:instno);
commit;
end;
/
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. 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();
For more
information on Oracle DBMS_JOB
see the following links:
|
