Tuesday, November 08, 2011

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>

No comments: