Examples of Using the Scheduler - Oracle 10g

This section discusses the following topics:

• Examples of Creating Jobs
• Examples of Creating Job Classes
• Examples of Creating Programs
• Examples of Creating Windows
• Examples of Setting Attributes
• Examples of Creating Jobs

This section contains several examples of creating jobs.

Creating a Job

The following statement creates a job called my _job1 in the oe schema:

BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'oe.my_job1', job_type => 'PLSQL_BLOCK', job_action => 'DBMS_STATS.GATHER_TABLE_STATS(''oe'',''sales'');', start_date => '15-JUL-03 1.00.00AM US/Pacific', repeat_interval => 'FREQ=DAILY', end_date => '15-SEP-03 1.00.00AM US/Pacific', enabled => TRUE, comments => 'Gather table statistics'); END; /

This job gathers table statistics on the sales table. It will run for the first time on July 15th and then once a day until September 15. To verify that the job was created, issue the following statement:

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB1'; JOB_NAME ------------------------------ MY_JOB1

Example Creating a Job

The following statement creates a job called my_job2 in the SYSTEM schema:

BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'system.my_job1', job_type => 'PLSQL_BLOCK', job_action => 'INSERT INTO sales VALUES( 7987, ''SALLY'', ''ANALYST'', NULL, NULL, NULL, NULL, NULL);', start_date => '28-APR-03 07.00.00.000000 AM Europe/Warsaw', repeat_interval => 'FREQ=HOURLY;INTERVAL=2', /* every two hours */ end_date => '20-NOV-04 07.00.00.000000 AM Europe/Warsaw', comments => 'My new job'); END; /

Examples of Creating Job Classes

This section contains several examples of creating job classes.

Creating a Job Class

The following statement creates a job class:

BEGIN DBMS_SCHEDULER.CREATE_JOB_CLASS ( job_class_name => 'my_class1', service => 'my_service1', comments => 'This is my first job class'); END; /

This creates my_class1 in SYS. It uses a service called my_service1. To verify that the job class was created, issue the following statement:

SELECT JOB_CLASS_NAME FROM DBA_SCHEDULER_JOB_CLASSES WHERE JOB_CLASS_NAME = 'MY_CLASS1'; JOB_CLASS_NAME ------------------------------ MY_CLASS1

Creating a Job Class

The following statement creates a job class:

BEGIN DBMS_SCHEDULER.CREATE_JOB_CLASS ( job_class_name => 'finance_jobs', resource_consumer_group => 'finance_group', comments => 'My financial class'); END; /

This creates finance_jobs in SYS. It uses a resource consumer group called finance_group.

Examples of Creating Programs

This section contains several examples of creating programs.

Creating a Program

The following statement creates a program in the oe schema:

BEGIN DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'oe.my_program1', program_type => 'PLSQL_BLOCK', program_action => 'DBMS_STATS.GATHER_TABLE_STATS(''oe'',''sales'');', number_of_arguments => 0, enabled => TRUE, comments => 'My comments here'); END; /

This creates my_program1, which uses PL/SQL to gather table statistics on the sales table. To verify that the program was created, issue the following statement:

SELECT PROGRAM_NAME FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'MY_PROGRAM1'; PROGRAM_NAME ------------------------- MY_PROGRAM1

Creating a Program

The following statement creates a program in the oe schema:

BEGIN DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'oe.my_saved_program1', program_action => '/usr/local/bin/date', program_type => 'EXECUTABLE', comments => 'My comments here'); END; /

This creates my_saved_program1, which uses an executable.

Examples of Creating Windows

This section contains several examples of creating windows.

Creating a Window

The following statement creates a window called my_window1 in SYS:

BEGIN DBMS_SCHEDULER.CREATE_WINDOW ( window_name => 'my_window1', resource_plan => 'my_res_plan1', start_date => '15-JUL-03 1.00.00AM US/Pacific', repeat_interval => 'FREQ=DAILY', end_date => '15-SEP-03 1.00.00AM US/Pacific', duration => interval '80' MINUTE, comments => 'This is my first window'); END; /

This window will open once a day at 1AM for 80 minutes every day from May 15th to October 15th. To verify that the window was created, issue the following statement:

Creating a Window

The following statement creates a window called my_window2 in SYS:

BEGIN DBMS_SCHEDULER.CREATE_WINDOW ( window_name => 'my_window2', schedule_name => 'my_stats_schedule', resource_plan => 'my_resourceplan1', duration => interval '60' minute, comments => 'My window'); END; /

Example of Creating Window Groups

The following statement creates a window group called my _window _group1:

BEGIN DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('my_windowgroup1'); END; /

Then, you could add three windows (my _window1, my _window2, and my _window3) to my_window_group1 by issuing the following statements:

BEGIN DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ( group_name => 'my_window_group1', window_list => 'my_window1, my_window2'); DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER ( group_name => 'my_window_group1', window_list => 'my_window3'); END; /

To verify that the window group was created and the windows added to it, issue the following statement:

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS; WINDOW_GROUP_NAME ENABLED NUMBER_OF_WINDOWS COMMENTS ----------------- ------- ----------------- --------------- MY_WINDOW_GROUP1 TRUE 3 This is my first window group

Examples of Setting Attributes

This section contains several examples of setting attributes.

Setting the Frequency Attribute

The following example resets the frequency my _emp _job1 will run to daily:

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'my_emp_job1', attribute => 'repeat_interval', value => 'FREQ=DAILY'); END; /

To verify the change, issue the following statement:

SELECT JOB_NAME, REPEAT_INTERVAL FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_EMP_JOB1'; JOB_NAME REPEAT_INTERVAL ---------------- --------------- MY_EMP_JOB1 FREQ=DAILY

Setting the Comments Attribute

The following example resets the comments for my_saved_program1:

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'my_saved_program1', attribute => 'comments', value => 'For nightly table stats'); END; /

To verify the change, issue the following statement:

SELECT PROGRAM_NAME, COMMENTS FROM DBA_SCHEDULER_PROGRAMS; PROGRAM_NAME COMMENTS ----------- ----------------------- MY_PROGRAM1 My comments here MY_SAVED_PROGRAM1 For nightly table stats

Setting the Duration Attribute

The following example resets the duration of my _window3 to 90 minutes:

BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'my_window3', attribute => 'duration', value => interval '90' minute); END; /

To verify the change, issue the following statement:

SELECT WINDOW_NAME, DURATION FROM DBA_SCHEDULER_WINDOWS WHERE WINDOW_NAME = 'MY_WINDOW3'; WINDOW_NAME DURATION ----------- --------------- MY_WINDOW3 +000 00:90:00