 |
|
Oracle
Scheduler Job Chain
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
A chain is a named series of programs that are
linked together for a combined objective. Each position within a
chain of interdependent programs is referred to as a step. Each step
can point to one of the following: a program, another chain (a
nested chain), an event.
Note: This feature introduced in Oracle 10g
release 2.
To create and use a chain:
1. Create a chain object
DBMS_SCHEDULER.CREATE_CHAIN (
CHAIN_NAME => 'bulk_load_chain',
RULE_SET_NAME => NULL,
EVALUATION_INTERVAL => NULL,
COMMENTS => 'Load data and run reports')
2. Define one or more chain steps. You define a step
that points to a program or nested chain.
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'do_bulk_load',
PROGRAM_NAME => 'hr.load_data_prog)
Also you can define a step that waits for an event
to occur by using the DEFINE_CHAIN_EVENT_STEP procedure. Procedure
arguments can point to an event schedule or can include an in-line
queue specification and event condition.
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'stop_when_disk_full_evt'
EVENT_SCHEDULE_NAME => 'disk_full_sched')
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'load_data_evt',
EVENT_CONDITION =>
'tab.user_data.object_owner=''HR'' and
tab.user_data.object_name = ''DATA.TXT'' and
tab.user_data.event_type =''FILE_ARRIVAL'' ',
QUEUE_SPEC => 'HR.LOAD_JOB_EVENT_Q')
3. Define chain rules. Each rule has a condition
and an action.
If the condition evaluates to TRUE, the action is
performed. Conditions are usually based on the outcome of one or
more previous steps. A condition accepts Boolean and numeric integer
values in an expression.
The entire expression must evaluate to a Boolean
value.
The simplified syntax of a chain condition is as
follows:
'factor|NOT(factor)[AND|OR
factor]'
factor:
stepname
ERROR_CODE number|[NOT]step_condition
When creating a rule condition using the simplified
syntax:
• You specify one or more factors, and a Boolean
operator (AND, OR, or NOT).
• A factor can be either a simple Boolean value
(TRUE or FALSE) or a chain condition. A chain condition describes
the condition of another step in the job chain. You can use the
following to describe the chain condition:
o The current state of the chain step:
SUCCEEDED
FAILED
STOPPED
COMPLETED
o The error code returned by the chain step. The
error is a numeric value, and can be:
Evaluated
within a numeric clause
Compared to
a list of values using an IN clause
You can use negative factors, by enclosing the
factor in parentheses and prefixing the factor with the NOT
operator.
Examples:
'step1 SUCCEEDED
AND step2 ERROR_CODE = 3'
'TRUE'
'step3 NOT COMPLETED AND NOT (step1 SUCCEEDED)'
'step2 ERROR_CODE NOT IN (1,2,3)'
You can also refer to attributes of chain steps of
the chain (this is called bind-variable syntax). The syntax is as
follows:
STEP_NAME.ATTRIBUTE
• Possible attributes are: completed, state
start_date, end_date, error_code, and duration.
• Possible values for the state attribute include:
'NOT_STARTED',
'SCHEDULED', 'RUNNING',
'PAUSED', 'SUCCEEDED', 'FAILED', and
'STOPPED'.
• If a step is in the state 'SUCCEEDED', 'FAILED',
or 'STOPPED', its completed attribute is set to 'TRUE'; otherwise,
completed is 'FALSE'.
Some examples of the bind variable syntax are:
':step1.state=''SUCCEEDED'' and
:step2.error_code=3'
'1=1'
':step3.state != ''COMPLETED'''
':step2.error_code not in (1,2,3)'
':step1.state = ''NOT_STARTED'''
The rule action specifies what is to be done as a
result of the rule being triggered. A typical action is to run a
specified step. Possible actions include:
o START step_1[,step_2...]
o STOP step_1[,step_2...]
o END [{end_value | step_name.error_code}]
When the job starts and at the end of each step, all
rules are evaluated to see what action or actions occur next. You
can also configure rules to be evaluated at regular intervals by
using the EVALUATION_INTERVAL attribute of the chain.
You add a rule to a chain with the DEFINE_CHAIN_RULE
procedure:
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'TRUE', -- starting step
ACTION => 'START load_data_evt,
stop_when_disk_full_evt',
Rule_Name => 'dataload_rule1',
COMMENTS => 'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'load_data_evt COMPLETED',
ACTION => 'START do_bulk_load',
RULE_NAME => 'dataload_rule2');
END;
4. Enable a chain with the ENABLE procedure (A chain
is always created disabled). Enabling an already enabled chain
does not return an error.
DBMS_SCHEDULER.ENABLE ('bulk_load_chain');
5. To run a chain, you must create a job of type
'CHAIN'. The job action must refer to the chain name.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'bulk_load_chain_job',
job_type => 'CHAIN',
job_action => 'bulk_load_chain',
repeat_interval => 'freq=daily;byhour=7',
enabled => TRUE);
END;
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |