 |
|
Oracle Tips by Burleson |
Chapter 4 -
Chaining Oracle Jobs Together
Conditional Job Runs using a Custom Table
Solution
The sequence of jobs is protected using the job_chain_locks
table. The RETRIES column specifies the number of times a task
should check the locks before it gives up and re-schedules itself.
The RETRY_DELAY column specifies the number of minutes between
retries. The TASK_NAME and LOCKED columns are self-explanatory.
job_chain_locks.sql
--
*************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial
purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial
purpose,
-- contact info@rampant.cc
--
*************************************************
CREATE TABLE job_chain_locks (
task_name VARCHAR2(20) NOT
NULL,
locked VARCHAR2(1) DEFAULT 'Y' NOT
NULL,
retries NUMBER(3) DEFAULT 0 NOT
NULL,
retry_delay NUMBER(3) DEFAULT 1 NOT
NULL,
CONSTRAINT job_chain_locks_pk PRIMARY KEY
(task_name)
);
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
