In Oracle 8i and 9i, you first need to have your JOB_QUEUE_PROCESSES initialization parameter set to a value larger than 0. This parameter specifies the number of SNPn (snapshot) processes that are responsible for running your PL/SQL jobs. This parameter can accept values from 0 to 36.
To manage your jobs in Oracle 8i and 9i, you can use the DBMS_JOB supplied package. The SUBMIT procedure of this package is used to create a job in the job queue. The following is an example of submitting a job to the job queue in SQL*Plus. This job will execute the REORG_PROC stored procedure today, and repeat it every week.
VARIABLE jobno EXEC DBMS_JOB.SUBMIT(:jobno, reorg_proc, - SYSDATE, SYSDATE + 7);Any stored procedure can be scheduled for execution in this manner.
The REMOVE procedure of the DBMS_JOB package can be used to remove a scheduled job from the job queue.
Oracle 10g introduced the DBMS Scheduler as a replacement for DBMS_JOB. The DBMS_SCHEDULER package has much more advanced functionality than DBMS_JOB. To schedule the same job above with DBMS_SCHEDULER, use the following command in SQL*Plus for your Oracle 10g database:
EXEC DBMS_SCHEDULER.CREATE_JOB ( - job_name => 'reorg_job', - job_type => 'STORED_PROCEDURE', - job_action => 'BEGIN reorg_proc(); END;' - start_date => SYSDATE, - repeat_interval => 'FREQ=WEEKLY');Additionally, Oracle 10g has a nice method of scheduling jobs with Oracle's Enterprise Manager (OEM), a GUI tool for managing your database.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading