DBMS_SCHEDULER

In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.

Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the privileges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the 'CREATE JOB' privilege.

With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

The basic elements of the Scheduler are:

Schedules

Jobs

Events

Chains

Programs

Job

A job instructs the scheduler to run a specific program at a specific time on a specific date.

Programs

A program contains the code (or reference to the code ) that needs to be run to accomplish a task. It also contains parameters that should be passed to the program at runtime. And it’s an independent object that can referenced by many jobs

Schedules

A schedule contains a start date, an optional end date, and repeat interval with these elements; an execution schedule can be calculated.

Windows

A window identifies a recurring block of time during which a specific resource plan should be enabled to govern resource allocation for the database.

Job groups

A job group is a logical method of classifying jobs with similar characteristics.

Window groups

A window groups is a logical method of grouping windows. They simplify the management of windows by allowing the members of the group to be manipulated as one object. Unlike job groups, window groups don’t set default characteristics for windows that belong to the group.

Using Job Scheduler

SQL> drop table emp;

SQL> Create table emp (eno int, esal int);

SQL > begin

dbms_scheduler.create_job (

job_name => 'test_babu',

job_type => 'PLSQL_BLOCK',

job_action => 'update emp set esal=esal*10 ;',

start_date => SYSDATE,

repeat_interval => 'FREQ=DAILY; INTERVAL=10',

comments => 'Babu tesing scheduler');

end;

PL/SQL procedure successfully completed.

Verification

To verify that job was created, the DBA ALL USER_SCHEDULER_JOBS view can be queried.

SQL> select job_name,enabled,run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT

------------------------------ ----- ----------

TEST_BABU FALSE 0

Note :

As you can see from the results, the job was indeed created, but is not enabled because the ENABLE attribute was not explicitly set in the CREATE_JOB procedure.

Run your job

SQL> begin

2 dbms_scheduler.run_job('TEST_BABU',TRUE);

3* end;

SQL> /

PL/SQL procedure successfully completed.

SQL> select job_name,enabled,run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT

------------------------------ ----- ----------

TEST_BABU FALSE 0

Copying Jobs

SQL> begin

2 dbms_scheduler.copy_job('TEST_BABU','NEW_TEST_BABU');

3 END;

4 /

PL/SQL procedure successfully completed.

SQL> select job_name,enabled,run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT

------------------------------ ----- ----------

TEST_BABU FALSE 0

NEW_TEST_BABU FALSE 0

SQL> begin

2 dbms_scheduler.stop_job(job_name => 'TEST_BABU', force => TRUE);

3* end;

SQL> /

begin

*

ERROR at line 1:

ORA-27366: job "SCOTT.TEST_BABU" is not running

ORA-06512: at "SYS.DBMS_ISCHED", line 164

ORA-06512: at "SYS.DBMS_SCHEDULER", line 483

ORA-06512: at line 2

Dropping Jobs

SQL> begin

2 dbms_scheduler.drop_job( job_name => 'NEW_TEST_BABU' , force => TRUE );

3 end;

4 /

PL/SQL procedure successfully completed.