DBMS_JOB

It must first be stated, or you might get confused as I did, that the DBMS_JOB package that was widely used in earlier versions of Oracle is now being left behind in Oracle 10g for the greater capabilities of the DBMS_SCHEDULER package. This new package allows us to manage jobs with greater versatility.

DBMS_SCHEDULER

This new job scheduler allows the database to handle a much fuller set of scheduling and monitoring capabilities. The vast amount of capabilities of the scheduler are beyond the scope of this article but I would encourage you to explore the manuals and determine which of the features will fit in your environment. I am confident you will switch from the old DBMS_JOBS to the new DBMS_SCHEDULER. If there was one feature that will get you to switch DBMS_SCHEDULER, it is its extended ability to handle a multitude of recurring time intervals. In addition, with this new scheduler we have the ability to control the execution of internal database applications as well as external applications. The new DBMS_SCHEDULER has three distinct parts.

Create a Program

As stated above, the program is a definition of what external program or application we will run. Listing 1 and Table 1 give you examples of how to define an external program to execute and what the parameters are used for. Listing 2 also gives you the external shell contents that are being executed from this scheduled program. Within this shell, you can execute anything that you would normally execute from within a shell script.

Listing 1.
Create a program to execute an external application

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name            => 'VMSTAT_PGM',
program_type            => 'EXECUTABLE',
program_action  => '/home/oracle/vmstat.sh',
enabled                 => TRUE,
comments                => 'generate vmstat output'
);
END;
/

Table 1.
CREATE_PROGRAM Parameters 

Parameter	Description
program_name	This parameter allows you to assign a unique name for the program.
Program_type	This parameter indicates the type of program that will be run. This type can take three values : plsql_block, stored_procedure, or for our example executable.
Program_action	This parameter defines what will be run.
enabled	This parameter is a flag that determines if the program should be enabled when created.
comments	Provide any comments to the schedule here.

Listing 2.
The External Program

[oracle@bier oracle]$ cat vmstat.sh
/usr/bin/vmstat >> /tmp/vmstat.LST

Creat a Job

Now that we have a program to execute defined, we must tell the database when to execute it. This is done by the creation of a job. Listing 3 and Table 3 give you an example of how to create the job and the parameters associated with the create statement.

Listing 3.
Creating a job to execute

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name          =>  'VMSTAT_JOB',
   program_name      =>  'VMSTAT_PGM',
   repeat_interval   =>  'FREQ=SECONDLY;INTERVAL=5',
   enabled            =>  TRUE,
   comments          =>  'Every 5 seconds');
END;
/

Table 3.
CREATE_JOB Parameters 

Parameter	Description
job_name	This parameter allows you to assign a unique name for the program you are creating.
program_name	This parameter allows you to associate a program name for the job you are creating.
repeat_interval	This parameter defines the recurring time interval for this job.
enabled	This parameter is a flag that determines if the job should be enabled when created.
comments	Add any comments to what you are doing here.

Next

Back to DBAsupport.com