|
- DROP TABLE scheduler_test;
- DROP SEQUENCE scheduler_test_seq;
- CREATE TABLE scheduler_test (
- id NUMBER(10) NOT NULL,
- description VARCHAR2(20) NOT NULL,
- created_date DATE NOT NULL,
- CONSTRAINT scheduler_test_pk PRIMARY KEY (id)
- );
- --------
- CREATE SEQUENCE scheduler_test_seq;
- -----
- BEGIN
- DBMS_SCHEDULER.create_program (
- program_name => 'test_program_1',
- program_type => 'PLSQL_BLOCK',
- program_action => 'BEGIN
- INSERT INTO scheduler_test (id, description, created_date)
- VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
- COMMIT;
- END;',
- enabled => TRUE,
- comments => 'Step2');
- DBMS_SCHEDULER.create_program (
- program_name => 'test_program_2',
- program_type => 'PLSQL_BLOCK',
- program_action => 'BEGIN
- INSERT INTO scheduler_test (id, description, created_date)
- VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
- COMMIT;
- END;',
- enabled => TRUE,
- comments => 'Step3');
- DBMS_SCHEDULER.create_program (
- program_name => 'test_program_3',
- program_type => 'PLSQL_BLOCK',
- program_action => 'BEGIN
- INSERT INTO scheduler_test (id, description, created_date)
- VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
- COMMIT;
- END;',
- enabled => TRUE,
- comments => 'Step4');
- END;
复制代码
- BEGIN
- sys.dbms_scheduler.create_chain(
- comments => 'chain1',
- chain_name => '"HR"."CHAIN1"');
- sys.dbms_scheduler.define_chain_event_step(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP1"',
- event_schedule_name => '"HR"."SCHEDULE4"');
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP1"',
- attribute => 'pause',
- value => FALSE);
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP1"',
- attribute => 'skip',
- value => FALSE);
- sys.dbms_scheduler.define_chain_step(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP2"',
- program_name => '"HR"."TEST_PROGRAM_1"');
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP2"',
- attribute => 'pause',
- value => FALSE);
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP2"',
- attribute => 'skip',
- value => FALSE);
- sys.dbms_scheduler.define_chain_step(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP3"',
- program_name => '"HR"."TEST_PROGRAM_2"');
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP3"',
- attribute => 'pause',
- value => FALSE);
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP3"',
- attribute => 'skip',
- value => FALSE);
- sys.dbms_scheduler.define_chain_step(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP4"',
- program_name => '"HR"."TEST_PROGRAM_3"');
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP4"',
- attribute => 'pause',
- value => FALSE);
- sys.dbms_scheduler.alter_chain(
- chain_name => '"HR"."CHAIN1"',
- step_name => '"STEP4"',
- attribute => 'skip',
- value => FALSE);
- sys.dbms_scheduler.define_chain_rule(
- chain_name => '"HR"."CHAIN1"',
- condition => '1=1',
- rule_name => 'RULE1',
- comments => 'Rule1',
- action => 'START Step1');
- sys.dbms_scheduler.define_chain_rule(
- chain_name => '"HR"."CHAIN1"',
- condition => 'Step1 SUCCEEDED',
- rule_name => 'RULE2',
- comments => 'Rule2',
- action => 'START Step2');
- sys.dbms_scheduler.define_chain_rule(
- chain_name => '"HR"."CHAIN1"',
- condition => 'Step2 SUCCEEDED',
- rule_name => 'RULE3',
- comments => 'Rule3',
- action => 'START Step3');
- sys.dbms_scheduler.define_chain_rule(
- chain_name => '"HR"."CHAIN1"',
- condition => 'Step3 SUCCEEDED',
- rule_name => 'RULE4',
- comments => 'Rule4',
- action => 'START Step4');
- sys.dbms_scheduler.define_chain_rule(
- chain_name => '"HR"."CHAIN1"',
- condition => 'Step4 SUCCEEDED',
- rule_name => 'RULE5',
- comments => 'Rule5',
- action => 'END');
- sys.dbms_scheduler.enable('"HR"."CHAIN1"');
- END;
复制代码 组装chain作业(普通的基于时间的Schedule):
- select * from dba_scheduler_global_attribute;
- select * from dba_scheduler_notifications;
- begin
- DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
- ('email_server','station90.example.com');
- end;
- begin
- DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE
- ('email_sender','oracle@example.com');
- end;
-
- begin
- DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
- job_name =>'HR.JOB5',
- events =>'job_succeeded,job_completed',
- recipients =>'oracle@example.com');
- end;
-
- begin
- DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION (
- job_name =>'HR.JOB5');
- end;
- 错误
- create or replace procedure hr.proc1
- begin
- update t123456 set a=2;
- end;
- begin
- DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
- job_name =>'HR.JOB51',
- recipients =>'oracle@example.com');
- end;
-
- BEGIN
- DBMS_SCHEDULER.add_job_email_notification (
- job_name => 'test_notification_job',
- recipients => 'oracle@example.com',
- events => 'job_failed',
- filter_condition => ':event.error_code=600');
- END;
- /
-
-
-
复制代码- BEGIN
- sys.dbms_scheduler.disable( '"HR"."JOB51"' );
- sys.dbms_scheduler.set_attribute( name => '"HR"."JOB51"', attribute => 'raise_events', value => 0);
- sys.dbms_scheduler.enable( '"HR"."JOB51"' );
- END;
复制代码
|
|