Oracle Scheduler Chain usage

Oracle Scheduler Chain usage

I am referring to other people's articles to learn about dbms_scheduler to create a CHAIN-type JOB. As for EXECUTABLE-type jobs, I have introduced how to <DBMS_SCHEDULER to execute PERL scripts to load data>. the following is an introduction to the Internet:

The Oracle CHAIN, that is, the CHAIN, connects a series of jobs to be completed, and determines which of the following actions needs to be completed based on the results of each step. Actually, if... then... else. For example, the following definition may exist: step 1 is successfully executed and step 2 is executed. If step 1 fails, step 3 is executed. If step 2 or step 3 is successfully executed, the entire chain is exited, if step 2 or step 3 fails, send the email and exit. The main steps of CHAIN include defining programs (what to do), defining CHAIN steps (total steps), and CHAIN rules (how to do ). This article describes the steps of chain and provides examples for your reference. For more information about jobs, see Oracle online documentation.

Main steps of CHAIN definition and implementation
1. Create a program (define the subroutine to be executed in each step and call DBMS_SCHEDULER.create_program)
2. Create a CHAIN (call DBMS_SCHEDULER.create_chain)
3. Define the CHAIN step (that is, the order of each step)
4. Define CHAIN rules (define the processing method after the execution result of each step is successful or fails)
5. Activate CHAIN
6. Add chain to job
7. Single-step debugging of CHAIN

Take the ETL_TEST user as an example to test:

1. Create a user and grant permissions

create user etl_test identified by etl_testdefault tablespace userstemporary 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 a test table and Sequence

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 CHAIN definition and implementation.

A. Create A program, create TEST_PRO_1, TEST_PRO_2, and TEST_PRO_3, and record the job running status to the test table.

begindbms_scheduler.create_program (program_name => 'TEST_PRO_1',program_type => 'PLSQL_BLOCK',program_action => 'begininsert 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 => 'begininsert 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 => 'begininsert 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 a CHAIN. In this step, only the CHAIN name is required.

begindbms_scheduler.create_chain (chain_name => 'TEST_CHAIN',rule_set_name => NULL,evaluation_interval => NULL,comments => 'test chain');end;

C. Define step chain to define every STEP of CHAIN and its corresponding PROGRAM_NAME, that is, what each STEP needs to do

begindbms_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. Define rules

begindbms_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. Activate CHAIN

begindbms_scheduler.enable ('TEST_CHAIN');end;

G. Add the CHAIN to the JOB for automatic or manual operation, or use RUN_CHAIN to give a JOB name to run

1. Create a JOB to run automatically

begindbms_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

begindbms_scheduler.create_job (job_name => 'TEST_CHAIN_MANU_JOB',job_type => 'CHAIN',job_action => 'TEST_CHAIN',enabled => FALSE);end;begindbms_scheduler.enable(name => 'TEST_CHAIN_MANU_JOB');end;

3. Run the command in RUN_CHAIN mode. a job name can be specified.

begindbms_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. delete a scheduler job

begindbms_scheduler.drop_job(job_name => 'TEST_CHAIN_MANU_JOB');end;

B. Clear the running logs of scheduler job

Begin
Dbms_scheduler.purge_log ();
End;

C. delete a chain

Begin
Dbms_scheduler.drop_chain (chain_name => 'test _ chain ');
End;

D. Delete the 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;

 

Query CHAIN-related statuses and views

A. view the running status view of scheduler job: user_scheduler_job_run_details

B. view the scheduler job view: user_scheduler_jobs

C. view the scheduler chain view: user_scheduler_chains

D. view the scheduler run view: user_scheduler_chain_rules

E. view the scheduler step view: user_scheduler_chain_steps

F. view the shed1_program view: user_scheduler_programs