Question: I have stopped a running chain with
the dbms_scheduler.stop_job procedure.
begin
sys.dbms_scheduler.stop_job (
job_name =>'zztop1',
force => true
);
end;
/
The problem is when I try to restart the job chain with
dbms_scheduler.alter_running_chain, I get the error that there is no
running job with this name.
sys.dbms_scheduler.alter_running_chain('zzyop01',
's50', 'state', 'not_started');
How do I stop a job chain and re-start the chain at the stopped
job step?
Answer: I would look at pausing the job
step instead of stopping it . . .
sys.dbms_scheduler.alter_running_chain('zzyop01',
's50',
'state',
'paused’);
Oracle has a great tool for suspending job chains via
dbms_scheduler.alter_running_chain “pause=true”, which allows all
steps in a job chain to stop and be re-started, at the point
immediately after the last job step completes.
You can use dbms_scheduler.alter_running_chain “pause=true” to
suspend execution in many places in a job chain, and re-start the
job chain at-will.
If the dbms_scheduler.alter_running_chain “pause=true” is set,
the job will stop right after it has completed execution and
“completed=false” remains set.
Conversely, if the dbms_scheduler.alter_running_chain
“pause=false” is set, the job will stop right after it has
completed execution and “completed=true” remains set.
This is why your call to set state = ‘not started” with
dbms_scheduler.alter_running_chain failed . . .
Because the job was already stopped.
When starting Oracle after an instance failure, Oracle will make
a consistent database by applying undo logs for a in-flight
transactions chain job steps.
Remember that a job stops that were running at the time of the
failure are marked as stopped and the chain continues processing at
the point of the last completed chain step.
You can have Oracle job chain steps restart automatically after a
database recovery by using alter_chain directive to set the
restart_on_recovery attribute to true for those steps.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
