In Oracle 8i and 9i, you first need to have your JOB_QUEUE_PROCESSES initialization parameter set to a value larger...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.