Convert your database JOB into scheduler by DBMS_SCHEDULER

The dbms_scheduler package was introduced in Oracle 10g. The example code shows how objects can be created, manipulated and dropped.  In a number of cases, code examples rely on previously created objects, which may have already been dropped, so they will have to be recreated before it will be possible to move on.

 

The following section will detail how to set up a test environment to enable the running of any example code.

Setting up a Test Environment.

In order to use the examples in this chapter, it is necessary to create a user ID to work with and define a task to schedule.  The following code creates a user called job_user and grants it the necessary privileges.  Some privileges used are specific for Oracle 11g and should be ignored if a prior version is used.

conn sys/password as sysdba 

 -- Create user. 

 create user job_user identified by job_user default tablespace users quota unlimited on users; 

 grant connect to job_user; 

 grant select_catalog_role to job_user; 

 -- Privileges for task, not for dbms_job. 

 grant create procedure to job_user; 

 grant execute on dbms_lock to job_user; 

 grant execute on dbms_system to job_user; 

 -- Oracle 10g only. 

 grant create job to job_user; 

 grant manage scheduler to job_user; 

 conn job_user/job_user; 

The MANAGE SCHEDULER privilege should only be granted when a user must administer job classes, windows and window groups.  These objects provide a link between the scheduler and the resource manager, a feature which had traditionally required the DBA role.  The roles and privileges associated with the 11g scheduler will be presented in the following text.

 In the previous script, a system privilege and an object privilege were granted to job_user to allow the creation of a task to schedule.  The following script creates a database procedure that will be used throughout this book when creating jobs.  This procedure uses the dbms_system package to write a user defined string to the alert log at the start and end of the job.  

 The body of the procedure loops 100 times with a sleep of one second in each loop.  It also uses the dbms_application_info package to write information to the v$session and v$session_longops views.  The use of the dbms_system and dbms_application_info packages will be covered in more detail later in this text. 

-- *****************************************************************

-- Parameters:

--    1) Text to identify this test job.

-- *****************************************************************

CREATE OR REPLACE PROCEDURE my_job_proc (p_text IN VARCHAR2) AS  

  l_rindex PLS_INTEGER;  

  l_slno  PLS_INTEGER;  

  l_total  NUMBER;  

  l_obj   PLS_INTEGER;  

 BEGIN  

  SYS.DBMS_SYSTEM.ksdwrt(2, 'MY_JOB_PROC Start: ' || p_text);  

  DBMS_APPLICATION_INFO.set_module(  

   module_name => 'my_job_proc',  

   action_name => p_text || ': Start.');  

  l_rindex  := Dbms_Application_Info.Set_Session_Longops_Nohint;  

  l_total := 100;  

  FOR i IN 1 .. l_total LOOP  

   DBMS_APPLICATION_INFO.set_action(  

    action_name => p_text || ': Sleep ' || i || ' of ' || l_total || '.');  

   DBMS_APPLICATION_INFO.set_session_longops(  

    rindex   => l_rindex,   

    slno    => l_slno,  

    op_name   => 'MY_JOB_PROC',   

    target   => l_obj,   

    context   => 0,   

    sofar    => i,   

    totalwork  => l_total,   

    target_desc => 'MY_JOB_PROC',   

    units    => 'loops');  

   DBMS_LOCK.sleep(1);  

  END LOOP;  

  DBMS_APPLICATION_INFO.set_action(  

   action_name => p_text || ': End.');  

  SYS.DBMS_SYSTEM.ksdwrt(2, 'MY_JOB_PROC End: ' || p_text);  

 END;  

 /  

 SHOW ERRORS  

The procedure can be tested by calling it from SQL*Plus as follows:

SQL> exec my_job_proc('Test It!');  

Once the procedure has completed, the alert log should contain an entry that looks similar to the following:

 Sat Jun 19 12:29:16 2004  

 MY_JOB_PROC Start: Test It!  

 Sat Jun 19 12:30:59 2004  

 MY_JOB_PROC End: Test It! 

Now that the user named job_user has been created and granted privileges, it is time to schedule jobs.  The first step is the examination of the dbms_job package. 

Oracle Job Schedulers examples

The following code examples rely on the previously defined programs and schedules to show how the overloads of the create_job procedure are used.

BEGIN  

  -- Job defined entirely by the CREATE JOB procedure.  

  DBMS_SCHEDULER.create_job (  

   job_name    => 'test_full_job_definition',  

   job_type    => 'PLSQL_BLOCK',  

   job_action   => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',  

   start_date   => SYSTIMESTAMP,  

   repeat_interval => 'freq=hourly; byminute=0',  

   end_date    => NULL,  

   enabled     => TRUE,  

   comments    => 'Job defined entirely by the CREATE JOB procedure.');  

 END;  

 /  

 BEGIN  

  -- Job defined by an existing program and schedule.  

  DBMS_SCHEDULER.create_job (  

   job_name   => 'test_prog_sched_job_definition',  

   program_name => 'test_plsql_block_prog',  

   schedule_name => 'test_hourly_schedule',  

   enabled    => TRUE,  

   comments   => 'Job defined by an existing program and schedule.');  

 END;  

 /  

 BEGIN  

  -- Job defined by an existing program and inline schedule.  

  DBMS_SCHEDULER.create_job (  

   job_name    => 'test_prog_job_definition',  

   program_name  => 'test_plsql_block_prog',  

   start_date   => SYSTIMESTAMP,  

   repeat_interval => 'freq=hourly; byminute=0',  

   end_date    => NULL,  

   enabled     => TRUE,  

   comments    => 'Job defined by existing program and inline schedule.');  

 END;  

 /  

 BEGIN  

  -- Job defined by existing schedule and inline program.  

  DBMS_SCHEDULER.create_job (  

    job_name   => 'test_sched_job_definition',  

    schedule_name => 'test_hourly_schedule',  

    job_type   => 'PLSQL_BLOCK',  

    job_action  => 'BEGIN my_job_proc(''CREATE_PROGRAM (BLOCK)''); END;',  

    enabled    => TRUE,  

    comments   => 'Job defined by existing schedule and inline program.');  

 END;  

 /  

ALTER_CHAIN Procedure

This procedure alters specified steps of a chain. This affects all future runs of the specified steps.

Syntax:

DBMS_SCHEDULER.ALTER_CHAIN (  

   chain_name       IN VARCHAR2,  

   step_name        IN VARCHAR2,  

   attribute        IN VARCHAR2,  

   value          IN BOOLEAN);  

ALTER_RUNNING_CHAIN Procedure

This procedure alters specified steps of a running chain. This will affect only steps of this running instance of the chain.

Syntax:

DBMS_SCHEDULER.ALTER_RUNNING_CHAIN (  

   job_name         IN VARCHAR2,  

   step_name        IN VARCHAR2,  

   attribute        IN VARCHAR2,  

   value            IN [BOOLEAN|VARCHAR2]);  

Few example related to DBMS_SCHEDULER:

To Restart a JOB

begin  

 dbms_scheduler.alter_running_chain(job_name => 'JOB_NAME',  

                  step_name => 'STEP_NAME',  

                  attribute => 'STATE',  

                  value   => 'NOT_STARTED');  

 end;  

To Skip a running Job

BEGIN  

dbms_scheduler.alter_running_chain 

               (chain_name  =>'CHAIN_NAME',  

                 step_name  =>'STEP_NAME',  

                 attribute  => 'SUCCEEDED',   

                 value      => TRUE);  

 END;  

 /  

To pause a running Job

BEGIN  

 dbms_scheduler.alter_chain('CHAIN_NAME','STEP_NAME','PAUSE',true);  

END;  

/ 

To Disable or Enable a Chain

BEGIN  

  DBMS_SCHEDULER.DISABLE ('SCHEDULER_NAME');  

 END;  

 /  

 BEGIN  

  DBMS_SCHEDULER.ENABLE ('SCHEDULER_NAME');  

 END;  

 /