12 Replies Latest reply: Apr 11, 2013 4:39 PM by 934896 RSS

    DBMS_SCHEDULER

    934896
      Hi
      I have two stored procedures and I want to execute them at the same time(synchronously), at various intervals.

      I am using DBMS_SCHEDULER.

      Here is my code.
      DROP TABLE emp;

      CREATE TABLE emp(
      eno NUMBER,
      esal NUMBER
      );

      INSERT INTO emp values(1,1000);
      INSERT INTO emp values(1,1500);
      INSERT INTO emp values(1,2000);
      INSERT INTO emp values(1,2500);
      COMMIT;
      SELECT * FROM emp;


      CREATE OR REPLACE PROCEDURE updemp IS

      BEGIN
      UPDATE emp SET esal = esal*1.5;
      END;
      /

      Here is the first scheduler.
      DECLARE
      v_job_action varchar2(1000);
      BEGIN


      v_job_action := 'begin ' || 'updemp;
                               end;
                               commit;';

      DBMS_SCHEDULER.CREATE_JOB(
      job_name => 'test_abc1',
      job_type => 'PLSQL_BLOCK',
      job_action => v_job_action,
      start_date => '6-APR-13 5.00.00 PM',
      repeat_interval => 'FREQ = MINUTELY; INTERVAL=30; BYHOUR = 8,9,10,11,12,13,14,15,16,17,18,19,20',
      enabled => TRUE,
      comments => 'testing Scheduler');
      END;
      /

      This scheduler starts on '6-APR-13 5.00.00 PM' and runs every 30 minutes from 8 AM to 8 PM.

      I have another stored procedure and scheduler here is the code.

      DROP TABLE DEPT;
      CREATE TABLE dept(
      deptno NUMBER,
      DNAME VARCHAR2(30),
      LOC VARCHAR2(30)
      );

      INSERT INTO dept values(10,'ACCOUNTING','');
      INSERT INTO dept values(20,'SALES','');
      INSERT INTO dept values(30,'RESEARCH','');
      INSERT INTO dept values(40,'OPERATIONS','');

      COMMIT;

      SELECT * FROM dept;

      CREATE OR REPLACE PROCEDURE upddept IS

      BEGIN
      UPDATE DEPT set loc = 'NEW_YORK' WHERE deptno = 10;
           UPDATE DEPT set loc = 'DALLAS' WHERE deptno = 20;
           UPDATE DEPT set loc = 'BOSTON' WHERE deptno = 30;
           UPDATE DEPT set loc = 'CHICAGO' WHERE deptno = 40;
           
           COMMIT;
           
      END;
      /

      AND here is the DBMS_SCHEDULE code

      DECLARE
      v_job_name varchar2(80);
      v_job_action varchar2(1000);
      BEGIN


      v_job_action := 'begin ' || 'upddept;
                               end;
                               commit;';
      DBMS_SCHEDULER.CREATE_JOB(
      job_name => 'test_abc2',
      job_type => 'PLSQL_BLOCK',
      job_action => v_job_action,
      start_date => '6-APR-13 7.00.00 PM',
      repeat_interval => 'FREQ = MINUTELY; INTERVAL=30; BYHOUR = 8,9,10,11,12,13,14,15,16,17,18,19,20',
      enabled => TRUE,
      comments => 'testing Scheduler');

      END;
      /

      This one calls the second procedure every 30 mnutes from 8 am to 8 PM. This also starts at 6-APR-13 7.00.00 PM.
      These work fine, but is there a way that the procedures could be executed synchronously, from one scheduler and not two. I am trying not to have two dbms_scheduler.create job, could this task be accomplished by using one scheduler. Some thing like creating a group and then calling the group.

      I am new to DBMS_SCHEDULER. all the help would be appreciated.

      Thanks
        • 1. Re: DBMS_SCHEDULER
          934896
          No one seems to respond to my post, I hope I poseted it on the correct Discssion forum
          • 2. Re: DBMS_SCHEDULER
            spajdy
            Yes. Chain job.
            You create chain with two steps.
            Both step are started when job start and job ends when both steps are done.

            1/ create program for every step
            2/ create chain
            3/ create rules
            4/ create job using chain

            Simple example (dbadmin is name of my test DB user)
            BEGIN
                dbms_scheduler.create_chain(chain_name          => 'dbadmin.chain',
                                            rule_set_name       => NULL,
                                            evaluation_interval => NULL,
                                            comments            => 'Test chain');
            
                dbms_scheduler.create_program(program_name   => 'dbadmin.Chain_1a',
                                              program_type   => 'PLSQL_BLOCK',
                                              program_action => 'begin dbms_lock.sleep(1*60); end;',
                                              enabled        => TRUE,
                                              comments       => 'Chain job: step1a');
                dbms_scheduler.create_program(program_name   => 'dbadmin.Chain_1b',
                                              program_type   => 'PLSQL_BLOCK',
                                              program_action => 'begin dbms_lock.sleep(2*60); end;',
                                              enabled        => TRUE,
                                              comments       => 'Chain job: step1b');
            
            
                dbms_scheduler.define_chain_step('dbadmin.chain', 'step_1a', 'dbadmin.Chain_1a');
                dbms_scheduler.define_chain_step('dbadmin.chain', 'step_1b', 'dbadmin.Chain_1b');
            
                dbms_scheduler.define_chain_rule(chain_name => 'dbadmin.chain',
                                                 condition  => 'TRUE',
                                                 action     => 'START step_1a, step_1b',
                                                 rule_name  => 'dbadmin.step_1_rule');
                dbms_scheduler.define_chain_rule(chain_name => 'dbadmin.chain',
                                                 condition  => 'step_1a COMPLETED AND step_1b COMPLETED',
                                                 action     => 'END',
                                                 rule_name  => 'dbadmin.step_2_rule');
            
                dbms_scheduler.create_job(job_name        => 'dbadmin.job_chain',
                                          job_type        => 'CHAIN',
                                          job_action      => 'dbadmin.chain',
                                          start_date      => SYSDATE,
                                          repeat_interval => 'FREQ=minutely; INTERVAL=10;BYSECOND=0',
                                          enabled         => FALSE,
                                          comments        => 'Test chain job');
            END;
            /
            • 3. Re: DBMS_SCHEDULER
              934896
              Hi Thanks

              I really appreciate your help, been struggling with it for the last 4 days. Apprecaite your help.
              Just one question, You have

              program_action => 'begin dbms_lock.sleep(1*60); end;',

              and

              program_action => 'begin dbms_lock.sleep(2*60); end;',

              in two different dbms_scheduler.create_program, is dbms_lock.sleep the name of the proc, so if my procedures are updemp and upddept

              I should replace dbms_lock.sleep(1*60) by updemp and dbms_lock.sleep(2*60) with upddept.

              Thanks
              • 4. Re: DBMS_SCHEDULER
                spajdy
                Yes. You have to replace my sample of your code.
                I used sleep procedure only for simple simulation of "real" code duration.
                • 5. Re: DBMS_SCHEDULER
                  934896
                  ok got it, thanks,
                  the problem now is I get insufficient privilege error her is an example.


                  I am able to create jobs, do we have separate prive for dbms_scheduler.create_job and dbms_scheduler.create_chain

                  When I try to create chain this is what i get, guess have to get in touch with the DBA to get the privs

                  BEGIN
                  dbms_scheduler.create_chain(chain_name => 'my_chain1',
                  rule_set_name => NULL,
                  evaluation_interval => NULL,
                  comments => 'Test chain');
                  END;


                  Error report:
                  ORA-01031: insufficient privileges
                  ORA-06512: at "SYS.DBMS_ISCHED", line 4921
                  ORA-06512: at "SYS.DBMS_ISCHED", line 1097
                  ORA-06512: at "SYS.DBMS_SCHEDULER", line 1598
                  ORA-06512: at line 2
                  01031. 00000 - "insufficient privileges"
                  *Cause:    An attempt was made to change the current username or password
                  without the appropriate privilege. This error also occurs if
                  attempting to install a database without the necessary operating
                  system privileges.
                  When Trusted Oracle is configure in DBMS MAC, this error may occur
                  if the user was granted the necessary privilege at a higher label
                  than the current login.
                  *Action:   Ask the database administrator to perform the operation or grant
                  the required privileges.
                  For Trusted Oracle users getting this error although granted the
                  the appropriate privilege at a higher label, ask the database
                  administrator to regrant the privilege at the appropriate label.



                  Thanks, appreciate it
                  • 6. Re: DBMS_SCHEDULER
                    spajdy
                    When you check ORACLE doc about suplied dbms_scheduler package you found this:
                    >
                    Usage Notes

                    Creating a chain in one's own schema requires the CREATE JOB system privilege. Creating a chain in a different schema requires the CREATE ANY JOB system privilege. If no rule_set_name is given, a rule set and evaluation context will be created in the schema that the chain is being created in, so the user will need to have the privileges required to create these objects. See the DBMS_RULE_ADM.CREATE_RULE_SET and DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT procedures for more information.
                    >

                    There is role SCHEDULER_ADMIN that have necessary privileges. So let your DBA to grant it to your DB schema.
                    • 7. Re: DBMS_SCHEDULER
                      934896
                      How can I find out the chain programs and chain_steps were created successfully.

                      How to drop chains?
                      • 8. Re: DBMS_SCHEDULER
                        spajdy
                        There are lots of view in data dictonary related to Scheduler see result of
                        SELECT * FROM sys.dictionary WHERE table_name LIKE '%SCHEDULER%';
                        to drop chain use
                        dbms_scheduler.drop_chain
                        • 9. Re: DBMS_SCHEDULER
                          934896
                          Excellent, Thanks a lot
                          • 10. Re: DBMS_SCHEDULER
                            934896
                            Hi I followed your instructions.

                            I created a chain named SELFSERVE_CHAIN

                            BEGIN
                            dbms_scheduler.create_chain(chain_name => 'SELFSERVE_CHAIN',
                            rule_set_name => NULL,
                            evaluation_interval => NULL,
                            comments => 'To refresh Self Serve dimensions syncronously.');
                                                                    
                            END;
                            /

                            That worked fine.
                            I have 10 stored procedures that I have to to run at the same time and another one the 11th stored proc after all have been executed.


                            SO here I am creating program 11 programs

                            BEGIN
                            dbms_scheduler.create_program(program_name => 'Acct_vendor_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_ACCOUNTING_VENDOR_PKG.Refresh_Acct_Vendor_DIM; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1a');
                            dbms_scheduler.create_program(program_name => 'Application_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_APPLICATION_PKG.Refresh_Application_Dim; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1b');
                                                                    
                                 dbms_scheduler.create_program(program_name => 'Award_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_AWARD_PKG.Refresh_Award_dim; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1c');
                            dbms_scheduler.create_program(program_name => 'Award_Summary_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_AWARD_SUMMARY_PKG.Refresh_Award_Summary_dim; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1d');
                                 dbms_scheduler.create_program(program_name => 'Contact_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_CONTACT_PKG.Refresh_Contact_DIM; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1e');
                            dbms_scheduler.create_program(program_name => 'Ref_contact_type_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_REF_CONTACT_TYPE_MV_PKG.Refresh_REF_CONTACT_TYPE_DIM; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1f');
                                 dbms_scheduler.create_program(program_name => 'Solicitation_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_SOLICITATION_PKG.Refresh_Solicitation_DIM; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1g');
                            dbms_scheduler.create_program(program_name => 'User_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_USER_PKG.Refresh_User_DIM; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1h');
                                 dbms_scheduler.create_program(program_name => 'Vendor_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_VENDOR_PKG.Refresh_Vendor_DIM; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1i');
                            dbms_scheduler.create_program(program_name => 'wf_object_status_dim_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_WORKFLOW_PKG.Referesh_wf_object_status_dim; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step1j');
                                                                    
                                 dbms_scheduler.create_program(program_name => 'Application_Txn_Fact_prog',
                            program_type => 'PLSQL_BLOCK',
                            program_action => 'begin LOAD_APPLICATION_TXN_FACT_PKG.Refresh_Application_Txn_Fact; end;',
                            enabled => TRUE,
                            comments => 'Chain job: step2');

                            END;
                            /

                            That worked fine,
                            then create chain_steps

                            BEGIN
                            dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1a', program_name => 'Acct_vendor_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1b', program_name => 'Application_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1c', program_name => 'Award_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1d', program_name => 'Award_Summary_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1e', program_name => 'Contact_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1f', program_name => 'Ref_contact_type_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1g', program_name => 'Solicitation_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1h', program_name => 'User_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1i', program_name => 'Vendor_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step_1j', program_name => 'wf_object_status_dim_prog');
                                 dbms_scheduler.define_chain_step(chain_name =>'SELFSERVE_CHAIN', step_name=> 'step2', program_name => 'Application_Txn_Fact_prog');
                                 
                                 
                            END;
                            /          


                            I created chain rules, that is it starts step 1a , 1b , 1c, 1d ,1e, 1f, 1g, 1h,1j, at the same times , ie the 10 pprocedures get execueted at the same time. after all those 10 procs are executed, I start off the last proc. It all compiled fine.


                            BEGIN
                            dbms_scheduler.define_chain_rule(chain_name => 'SELFSERVE_CHAIN',
                            condition => 'TRUE',
                            action => 'START step_1a, step_1b, step_1c, step_1d, step_1e, step_1f, step_1g, step_1h,step_1i, step_1j',
                            rule_name => 'rule1',
                            comments => 'Start The chain');
                            dbms_scheduler.define_chain_rule(chain_name => 'SELFSERVE_CHAIN',
                            condition => 'step_1a COMPLETED AND step_1b COMPLETED AND step_1c COMPLETED AND step_1d COMPLETED AND step_1e COMPLETED AND step_1f COMPLETED AND step_1g COMPLETED AND step_1h COMPLETED AND step_1i COMPLETED AND step_1j COMPLETED',
                            action => 'START step2',
                            rule_name => 'rule2',
                            comments => 'Start refreshing application_txn_fact');

                            dbms_scheduler.define_chain_rule(chain_name => 'SELFSERVE_CHAIN',
                            condition => 'step2 COMPLETED',
                            action => 'END',
                            rule_name => 'rule3',
                                                                         comments => 'End refreshing dimensions and facts');
                            END;


                            I then created a job. that compiled too
                            This creates job.
                            BEGIN

                                 dbms_scheduler.create_job(job_name => 'SELFSERVE_CHAIN_JOB',
                            job_type => 'CHAIN',
                            job_action => 'SELFSERVE_CHAIN',
                            start_date => '10-APR-13 1.00.00 PM',
                            repeat_interval => 'FREQ = MINUTELY; INTERVAL=30; BYHOUR = 8,9,10,11,12,13,14,15,16,17,18,19,20',
                            enabled => TRUE,
                            comments => 'Refresh Self Serve Dimensions');
                            END;
                            /


                            However my jobs are failing, where to get message for failing jobs, how do I find out what went wrong

                            SELECT status, run_duration,REQ_START_DATE,ACTUAL_START_DATE, job_name
                            FROM dba_scheduler_job_run_details
                            WHERE job_name = 'SELFSERVE_CHAIN_JOB'
                            ORDER BY 3 desc;

                            The result of the jobs

                            FAILED     0 0:0:0.0     10-APR-13 02.00.00.000000000 PM AMERICA/NEW_YORK     10-APR-13 02.00.00.060413000 PM AMERICA/NEW_YORK     SELFSERVE_CHAIN_JOB
                            FAILED     0 0:0:0.0     10-APR-13 01.30.00.000000000 PM AMERICA/NEW_YORK     10-APR-13 01.30.00.560653000 PM AMERICA/NEW_YORK     SELFSERVE_CHAIN_JOB
                            FAILED     0 0:0:0.0     10-APR-13 01.00.00.000000000 PM AMERICA/NEW_YORK     10-APR-13 01.00.00.532246000 PM AMERICA/NEW_YORK     SELFSERVE_CHAIN_JOB
                            • 11. Re: DBMS_SCHEDULER
                              spajdy
                              In views:
                              dba_scheduler_job_run_details
                              dba_scheduler_job_log

                              there is column additional_info. See what is in this column for your job failed runs. It give you a glue what is wrong.
                              • 12. Re: DBMS_SCHEDULER
                                934896
                                ok thanks, you have been a great help