The DBMS_JOB Package

As an Oracle user, you can schedule jobs in the job queue as long as you have permission to execute the procedures within the DBMS_JOB package, provided by Oracle Corporation. The DBA must create the DBMS_JOB package by running the dbmsjob.sql script, which is located under ORACLE_HOME/rdbms/admin. This location is operating system-dependent. For example:

The procedures that are stored in the DBMS_JOB package include:

Each of these jobs has input and output parameters. As the person executing the procedure, you must supply a value for all input parameters, unless the parameter has a default. For instance, to force a job to run, you must supply the job number. The values for the output variables are supplied by Oracle. Later in this section, you will see examples of executing some of these procedures using the input/output parameters.

Job Attributes

All jobs that are placed in the job queue have certain attributes, including:

•  Job owner

•  Job number

•  Job definition

•  Job execution interval

Submitting and Manipulating Jobs

Submitting new jobs and manipulating current jobs in the job queues is quite a simple task with the array of procedures supplied in the DBMS_JOB package. Possible job queue actions include submitting a job to the job queue, removing a job from the job queue, changing a specific job’s attributes, designating a job as broken, and forcing a job to run. The following sections show examples of running the various procedures associated with managing jobs in an Oracle database.

---

NOTE:  
In order to submit, modify, and remove jobs from the job queues, you must either be a DBA or have been granted the EXECUTE privilege on the applicable procedures in the DBMS_JOB package.

---

Submitting Jobs

Jobs are submitted to a queue using the SUBMIT procedure of the DBMS_JOB package. First, a token procedure is created. The job is submitted, with four parameters passed into the procedure. The parameters are

SQL> create or replace procedure chk_extents as

 2  cursor c_extents is

 3   select segment_name, bytes, extents, max_extents

 4   from sys.dba_segments

 5   where extents > 10;

 6  rec c_extents%ROWTYPE;

 7 begin

 8  open c_extents;

 9  loop

 10   fetch c_extents into rec;

 11   exit when c_extents%NOTFOUND;

 12   insert into extent_stats

 13   values (rec.segment_name,rec.bytes,rec.extents,rec.max_extents,

      sysdate);

 14   commit;

 15  end loop;

 16  close c_extents;

 17 end;

/



Procedure created.

This procedure checks segments with more than 10 extents and inserts records for each segment meeting this criteria into the extent_stats table. This procedure (job) can be scheduled to run periodically. You can query the extent_stats table to measure the growth and frequency of database objects.

SQL> execute chk_extents



PL/SQL procedure successfully completed.

Although Oracle generates the job number, you need to know it to modify or remove the job. We have initialized a variable from the SQL> prompt called jobnum. Notice the way the job queue request is submitted:

SQL>variable jobnum number;



SQL> begin

  2     dbms_job.submit(:jobnum,’ryan.chk_extents;’,sysdate, ‘sysdate +

        1/2’);

  3 end;

  4 /





PL/SQL procedure successfully completed.

The SQL*Plus command EXECUTE may also be used to execute procedures, such as follows:

SQL> execute dbms_job.submit(:jobnum,’ryan.chk_extents;’,sysdate, ‘sysdate 

+ 1/2’)

SQL> print jobnum



   JOBNUM

---------

        1

The PRINT command was used to print the value of jobnum. The job number can also be derived from the SYS.DBA_JOBS and SYS.USER_JOBS data dictionary views. Also notice that the name of the procedure to run is in single quotations, as is the execution interval (the next date value is not in quotes). This job is scheduled to execute twice a day.

If you haven’t already considered it, it would be an excellent idea to schedule a job that will periodically archive the contents of the audit trail, and then purge the audit trail table.

SQL> create table aud_archive as

  2 select * from sys.aud$;



Table created.

An example PL/SQL routine (run as user SYS):

SQL> create or replace procedure p_aud_archive as

  2 begin

  3  insert into aud_archive

  4  select * from aud$;

  5  commit;

  6  delete from aud$;

  7  commit;

  8* end;

Once this procedure has been created, schedule it to run periodically using the DBMS_JOB.SUBMIT procedure, as shown previously.

Removing Jobs

Jobs can be removed from the job queue as easily as they are scheduled. The REMOVE procedure is used to remove a job currently in a queue:

SQL> begin

  2  dbms_job.remove(1);

  3 end;

  4 /



PL/SQL procedure successfully completed.