Usage of Oracle Scheduler Chain

I am borrowing to recommend others The purpose is to learn dbms_scheduler create chain type of job, as for executable type of job I have been in <dbms_scheduler execute perl script load data > introduced. The following to the online related:

Oracle CHAIN, which is a chain, connects a bunch of jobs that need to be done, depending on the outcome of each step to determine which of the following actions needs to be completed. It's actually the IF. Then.. Else For example, there may be the following definition, step 1 executed successfully, step 2 executed, if step 1 fails to execute Step 3, if step 2 or Step 3 is executed successfully, exit the entire chain, if step 2 or Step 3 fails, send the message and exit. The main steps of chain include defining the program (what to do), defining the chain step (the total step), and the Rules for chain (how to do that). This article mainly describes some of the steps of chain and gives examples for everyone to refer to. For Job,program, refer to Oracle online documentation.

chain definition and implementation of the main steps
1. Create the program (define subroutines that need to be executed for each step, call Dbms_scheduler.create_program)
2. Create chain (call Dbms_scheduler.create_chain)
3, define the chain step (also in the order of each step)
4. Define chain rules (define how the execution results of each step are handled successfully or after failure)
5. Activating chain
6. Add chain to Job
7, chain single-step debugging

I take the etl_test user as an example to test:

1. Create a user and give permissions

Create user etl_test identified by etl_test
Default Tablespace users
Temporary tablespace temp;

Grant create job to etl_test;
Grant Create evaluation context to etl_test;
Grant create rule set to Etl_test;
Grant create rule to etl_test;

2. Create test tables and sequences

CREATE TABLE Tb_schduler_chain
(
ID number,
Job_name VARCHAR2 (30),
Run_date Date
);

Create sequence tb_schduler_chain_seq;

3. The following are the main steps for the definition and implementation of chain

A. Creating a program, creating test_pro_1,test_pro_2,test_pro_3 three programs, logging job runs to a test table

Begin
Dbms_scheduler.create_program (
program_name = ' Test_pro_1 ',
Program_type = ' Plsql_block ',
Program_action = ' Begin
INSERT into Tb_schduler_chain (ID, job_name, run_date)
VALUES (tb_schduler_chain_seq.nextval, ' job_name_1 ', sysdate);
Commit
End; ',
Enabled = TRUE,
Comments = ' Program for first link in the chain. '
);


Dbms_scheduler.create_program (
program_name = ' Test_pro_2 ',
Program_type = ' Plsql_block ',
Program_action = ' Begin
INSERT into Tb_schduler_chain (ID, job_name, run_date)
VALUES (tb_schduler_chain_seq.nextval, ' job_name_2 ', sysdate);
Commit
End; ',
Enabled = TRUE,
Comments = ' Program for second link in the chain ');


Dbms_scheduler.create_program (
program_name = ' Test_pro_3 ',
Program_type = ' Plsql_block ',
Program_action = ' Begin
INSERT into Tb_schduler_chain (ID, job_name, run_date)
VALUES (tb_schduler_chain_seq.nextval, ' job_name_3 ', sysdate);
Commit
End; ',
Enabled = TRUE,
Comments = ' Program for last link in the chain. ');
End

B. Create chain, this step generally requires only chain name.

Begin
Dbms_scheduler.create_chain (
Chain_name = ' Test_chain ',
Rule_set_name = NULL,
Evaluation_interval = NULL,
Comments = ' Test chain '
);
End

C. Define step CHAIN Each step of the definition CHAIN and its corresponding program_name, i.e. what needs to be done at each step

Begin
Dbms_scheduler.define_chain_step (
Chain_name = ' Test_chain ',
Step_name = ' Chain_step_1 ',
program_name = ' test_pro_1 ');


Dbms_scheduler.define_chain_step (
Chain_name = ' Test_chain ',
Step_name = ' chain_step_2 ',
program_name = ' test_pro_2 ');


Dbms_scheduler.define_chain_step (
Chain_name = ' Test_chain ',
Step_name = ' Chain_step_3 ',
program_name = ' test_pro_3 ');
End

D. Defining rules

Begin
Dbms_scheduler.define_chain_rule (
Chain_name = ' Test_chain ',
Condition = ' TRUE ',
Action = ' START ' chain_step_1 "',
Rule_name = ' Chain_rule_1 ',
Comments = ' first link in the chain. ');
Dbms_scheduler.define_chain_rule (
Chain_name = ' Test_chain ',
Condition = ' "chain_step_1" Completed ',
Action = ' START ' chain_step_2 "',
Rule_name = ' chain_rule_2 ',
Comments = ' Second link in the chain ');


Dbms_scheduler.define_chain_rule (
Chain_name = ' Test_chain ',
Condition = ' "chain_step_2" Completed ',
Action = ' START ' Chain_step_3 "',
Rule_name = ' Chain_rule_3 ',
Comments = ' Third link in the chain ');


Dbms_scheduler.define_chain_rule (
Chain_name = ' Test_chain ',
Condition = ' "chain_step_3" Completed ',
Action = ' END ',
Rule_name = ' Chain_rule_4 ',
Comments = ' End of the chain. ');
End

F. Activating chain

Begin
Dbms_scheduler.enable (' Test_chain ');
End

G. Adding chain to a job to run automatically or manually or by Run_chain given a job name

1. Create a job to run automatically

Begin
Dbms_scheduler.create_job (
job_name = ' Test_chain_auto_job ',
Job_type = ' CHAIN ',
job_action = ' Test_chain ',
Repeat_interval = ' freq=minutely; Interval=2 ',
start_date = Systimestamp,
end_date = Systimestamp + (1/48),
Enabled = False);
End

2. Create a manual job and execute

Begin
Dbms_scheduler.create_job (
job_name = ' Test_chain_manu_job ',
Job_type = ' CHAIN ',
job_action = ' Test_chain ',
Enabled = FALSE);
End

Begin
Dbms_scheduler.enable (name = ' Test_chain_manu_job ');
End

3. Execute in Run_chain mode, can be given a job name

Begin
Dbms_scheduler.run_chain (chain_name = ' Test_chain ', start_steps = ' chain_step_1,chain_step_2,chain_step_3 ', job_name = ' test_chain_run_job ');
End

A. Deleting a scheduler job

Begin
Dbms_scheduler.drop_job (job_name = ' test_chain_manu_job ');
End

B. Clear the Scheduler job run log

Begin
Dbms_scheduler.purge_log ();
End

C. Delete Chain

Begin
Dbms_scheduler.drop_chain (chain_name = ' test_chain ');
End

D. Delete Program

Begin
Dbms_scheduler.drop_program (program_name = ' test_pro_1 ');
End

E. Delete rule

Begin
Dbms_scheduler.drop_chain_rule (chain_name = ' Test_chain ', rule_name = ' chain_rule_1 ');
End

F. Delete step

Begin
Dbms_scheduler.drop_chain_step (chain_name = ' Test_chain ', step_name = ' chain_step_1 ');
End

Chain related status and view query

A. View scheduler job Run Status View: User_scheduler_job_run_details

B. View Scheduler job View: User_scheduler_jobs

C. View Scheduler chain view: User_scheduler_chains

D. View Scheduler Run View: user_scheduler_chain_rules

E. View scheduler Step view: User_scheduler_chain_steps

F. View Sheduler program view: User_scheduler_programs

Usage of Oracle Scheduler Chain