| |
Scripts Developed for New
dbms_scheduler Utility
November 1,
2004
Oracle Guru
Jim Czuprynski has just
published a good article on the exciting new dbms_scheduler package:
Jim notes that dbms_scheduler is far
more powerful than the dbms_jobs package and he provides some very
useful techniques and scripts:
Basic Task Scheduling.
First off, fear not! The Scheduler does keep the basic functionality
of DBMS_JOB intact:
- A task can be scheduled to run at a particular date and
time.
- A task can be scheduled to run only once, or
multiple times.
- A task can be turned off temporarily or removed
completely from the schedule.
- Complex scheduling is still available, but now much
simpler. (For example, DBMS_JOB could be manipulated into running
a task every Tuesday, Thursday and Saturday at 08:00, but it did
take some experimentation with NEXT_DATE and the INTERVAL
parameter of DBMS_JOB before I got it right.)
Jim also has a follow-up article that
shows even more powerful dbms_scheduler features:
Best of all, Czuprynski shows a
suggested series of steps for converting from dbms_jobs to
dbms_scheduler:
Transitioning from DBMS_JOB to DBMS_SCHEDULER
To make the transition from using
DBMS_JOB to DBMS_SCHEDULER, I suggest the following steps:
Identify all currently active
scheduled tasks that were scheduled via DBMS_JOB, including their
scheduled run times.
Analyze these tasks and identify
any "overlaps" among the DBMS_JOB scheduled tasks. These overlaps
might include identical tasks being run at different times;
tasks being run in parallel; or tasks being run
serially.
Convert tasks scheduled via
DBMS_JOB into their corresponding DBMS_SCHEDULER components
(program, schedule, and job objects):
"Break" the currently scheduled task
to make sure that duplicate tasks don't fire off at the same time:
BEGIN
DBMS_JOB.BROKEN(<job>, TRUE);
...
COMMIT;
END;
/
- If the task is essentially a
stand-alone task, use DBMS_SCHEDULE.CREATE_JOB to
create a new instance of the task.
- If several tasks can benefit from
reuse, then, use DBMS_SCHEDULE.CREATE_JOB,
DBMS_SCHEDULE.CREATE_PROGRAM, and
DBMS_SCHEDULE.CREATE_JOB to create the required
components and start them running on schedule.
Once the conversion is complete,
then remove all the tasks using DBMS_JOB.REMOVE:.
BEGIN
DBMS_JOB.REMOVE(<job>, TRUE);
...
COMMIT;
END;
/
|
|
|
|
and include the URL for the page.
