DBMS_JOB

I decided to do a worked example with DBMS_JOB as I had not used it before. This was done on an Oracle 9 database. DBMS_JOB was superseded by DBMS_SCHEDULER in Oracle 10. In the documentation for version 10, Oracle says that ... you should consider disabling DBMS_JOB by revoking the package execution privilege for users ...

First, create a table to store the output from the job I am about to create:

SQL> create table job_record

  2  (date_and_time date)

  3  /

 

Table created.

 

SQL>

Then note the start time:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')

  2  time_now from dual

  3  /

 

TIME_NOW

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

14-JUL-11 14:36:18

 

SQL> 

The following query shows that no special permission is required to run DBMS_JOB:

SQL> select grantee from dba_tab_privs

  2  where owner = 'SYS'

  3  and table_name = 'DBMS_JOB'

  4  /

 

GRANTEE

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

PUBLIC

 

SQL>

Now create a job to insert SYSDATE into the job_record table every minute. Oracle assigns the job the number 6:

SQL> variable job_no number;

SQL> exec dbms_job.submit( -

> job       => :job_no, -

> what      => 'begin insert into job_record -

>               values(sysdate); end;', -

> next_date =>  sysdate + 1/1440, -

> interval  => 'sysdate + 1/1440');

 

PL/SQL procedure successfully completed.

 

SQL> print job_no

 

    JOB_NO

----------

         6

 

SQL> commit;

 

Commit complete.

 

SQL>

Confirm that parameter job_queue_processes is zero. This will stop the job running:

SQL> col value format a10

SQL> select value from v$parameter

  2  where name = 'job_queue_processes'

  3  /

 

VALUE

----------

0

 

SQL> 

Wait 5 minutes:

SQL> exec dbms_lock.sleep(300);

 

PL/SQL procedure successfully completed.

 

SQL>

Note the new time to show that 5 minutes have passed:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')

  2  time_now from dual

  3  /

 

TIME_NOW

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

14-JUL-11 14:41:18

 

SQL>

Check that no rows have been written to the table as job_queue_processes is zero:

SQL> select to_char(date_and_time,'DD-MON-YY HH24:MI:SS')

  2  job_log from job_record

  3  /

 

no rows selected

 

SQL> 

Now run the job manually to show that it works:

SQL> exec dbms_job.run(6);

 

PL/SQL procedure successfully completed.

 

SQL>

And query the row that has been written to the table:

SQL> select to_char(date_and_time,'DD-MON-YY HH24:MI:SS')

  2  job_log from job_record

  3  /

 

JOB_LOG

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

14-JUL-11 14:41:18

 

SQL> 

Set job_queue_processes to be greater than zero and wait another 5 minutes to see if the job starts to run automatically:

SQL> alter system set job_queue_processes = 1

  2  /

 

System altered.

 

SQL> exec dbms_lock.sleep(300);

 

PL/SQL procedure successfully completed.

 

SQL> 

Note the new time to show that another 5 minutes have passed:

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS')

  2  time_now from dual

  3  /

 

TIME_NOW

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

14-JUL-11 14:46:18

 

SQL>

And verify that several more rows have been written to the table as job_queue_processes is no longer zero:

SQL> select to_char(date_and_time,'DD-MON-YY HH24:MI:SS')

  2  job_log from job_record

  3  /

 

JOB_LOG

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

14-JUL-11 14:41:18

14-JUL-11 14:42:21

14-JUL-11 14:43:26

14-JUL-11 14:44:32

14-JUL-11 14:45:37

 

SQL> 

Finally, remove the job from the database:

SQL> exec dbms_job.remove(6);

 

PL/SQL procedure successfully completed.

 

SQL>