Introduction
DBMS_JOB allows a user to schedule a job to run at a specified time.
A job is submitted to a job queue and runs at the specified time.
The user can also input a parameter that specifies how often the job should
run. A job can consist of any PL/SQL code.
SNP Background Processes
SNP processes run in the background and implement database snapshots
and job queues. If an SNP process fails, Oracle restarts it without
affecting the rest of the database. An SNP process can run one job
at a time with a maximum of ten SNP processes running simultaneously.
The INIT.ORA initialization file contains three parameters that control
the behavior of the SNP processes:
| Parameters | Description |
| JOB_QUEUE_PROCESSES | How many processes to start. If set to zero, no jobs are executed. Default is 0. Range is 0..10. |
| JOB_QUEUE_INTERVAL | How long an interval the process will sleep before checking for a new job. Default is 60 sec. Range is 1..3600 sec |
| JOB_QUEUE_KEEP_CONNECTIONS | Controls whether an SNP process closes any remote database connections. Default is False. Range is True/False. |
Quick overview of Oracle_Jobs
Create a Job (run it every day)
VARIABLE v_jobnum NUMBER;
BEGIN
dbms_job.submit(:v_jobnum, 'MY_STORED_PROCEDURE;', sysdate,'sysdate+1');
END;
Run an Existing Job
dbms_job.run(job_number);
Remove a Job from the Job Queue
dbms_job.remove(job_number);
Review Job Status
select substr(log_user, 1,7) log_user, substr(priv_user,1,7) priv_user,
substr(schema_user,1,7) schema,
last_date, next_date, failures fail#,
substr(interval,1,35) interval,
total_time, substr(what,1,20) what
from dba_jobs;
Examples
Monday through Friday Example
The following example shows how to schedule a job to execute Monday
through Friday:
DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
NEXT_DAY(SYSDATE,''TUESDAY''),
NEXT_DAY(SYSDATE,''WEDNESDAY''),
NEXT_DAY(SYSDATE,''THURSDAY''),
NEXT_DAY(SYSDATE,''FRIDAY'') ))');
The following example shows how to schedule a job to execute Monday
through Friday at 6:00 pm:
DBMS_JOB.SUBMIT (:v_jobnum,'my_procedure;', sysdate,
'TRUNC(LEAST(NEXT_DAY(SYSDATE,''MONDAY''),
NEXT_DAY(SYSDATE,''TUESDAY''),
NEXT_DAY(SYSDATE,''WEDNESDAY''),
NEXT_DAY(SYSDATE,''THURSDAY''),
NEXT_DAY(SYSDATE,''FRIDAY'') )) + 18/24');
Notes:
** Two single quotes are used around the day of the week, not double
quotes.
** Ensure that the 4th parameter is all on one line. Allow the
line to wrap if necessary. The 4th parameter only allows 200 characters.
Several white spaces can cause this limit to be reached quickly.
Midnight Example
The following example shows how to schedule a job to execute at midnight
every night:
DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate) + 1,'sysdate + 1');
The 'trunc(sysdate) + 1' sets the time back to the current day, midnight,
and informs the scheduler to start the job at the following midnight.
The 'sysdate + 1' sets the interval to 1 day. This job would
run the first time at midnight, then every midnight afterwards.
To schedule the job at 8 a.m., specify the next_date parameter as trunc(sysdate)
+ 1 + 8/24.
Daily Example
The following example shows how to schedule a job to execute every
day at the same time:
DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate)+?.??/24,'trunc(sysdate+1)+?.??/24');
To run the job at the same time every day, make sure that you pass the same value '?.??/24' to both the 'next_date' and 'interval' parameters. The value returned by trunc(sysdate) is midnight today and the value returned by trunc(sysdate + 1) is midnight tomorrow.
Hourly Example
If the interval between execution should be 1 hour, then the job must
be submitted as
EXEC DBMS_JOB.SUBMIT(:jobno,'MY_PROCEDURE;', SYSDATE, 'SYSDATE
+ 1/24');
To specify the time, say every half past hour the procedure has to execute,
dbms_job.submit(:jobno,'my_procedure;',to_date('28/nov/00 15:30:00',
'dd/mon/yy HHH4:MI:SS'),'sysdate+1/24');
Start at Specific Time and run it every Sunday at 9 AM
DBMS_JOB.SUBMIT (:v_jobnum, 'MY_PROCEDURE;', TO_DATE('0409200009','DDMMYYYYHH24'),
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')');
or
DBMS_JOB.SUBMIT (:v_jobnum, 'MY_PROCEDURE;', to_date('15-Aug-2002 12:00','dd-Mon-yyyy hh24:mi'),
'NEXT_DAY(TRUNC(SYSDATE),''SUNDAY'')');
Example of Monthly execution
dbms_job.submit(:v_jobnum, 'PROCEDURE;', to_date('15-Aug-2002 12:00','dd-Mon-yyyy hh24:mi'),
sysdate+to_char(last_day(sysdate),'dd'));
Restore a Broken Job
exec dbms_job.broken(#, false);
commit;
Import/Export:
Jobs can be imported and exported. If a job is defined in one
database, it can be transferred to another database. This does not
change the job number, environment, or definition.
Job Owner:
The person who submits a job to the job queue is considered the job's
owner. The job's owner is the only one who can alter the job, force the
job to run,
or remove the job from the queue. If you need to look up any
information regarding a submitted job, DBA_JOBS and USER_JOBS contain a
lot of
information such as the job number, user, status, etc.
Job Definitions:
The WHAT parameter of the SUBMIT procedure specifies the job definition.
The WHAT parameter should be a string that calls the stored procedure to
be run. There can be any number of parameters. All parameters
should be IN parameters. The only exceptions to this rule are the
special identifiers next_date and broken. Job definitions should
be enclosed in single quotes and terminated with a semicolon.
DBMS_JOB.SUBMIT(1234,
'employee_stats(''PROGRAMMER'',''FULL_TIME'',''HIRE_DATE'');',
SYSDATE,
SYSDATE + 7);
The DBMS_JOB Package
Below are a list of procedures available with the DBMS_JOB package.
Most procedures in this package require a COMMIT before the changes are
noticed by
the SNP processes.
Procedure SUBMIT
The submit procedure submits a new job to the job queue. The
following is the header for the procedure:
PROCEDURE DBMS_JOB.SUBMIT
(job
OUT BINARY_INTEGER,
what
IN VARCHAR2
NEXT_DATE
IN DATE DEFAULT SYSDATE,
Interval
IN VARCHAR2 DEFAULT 'null',
No_parse
IN BOOLEAN DEFAULT FALSE);
The parameters are summarized as follows:
Parameter Description
Job
Unique identifier of the job
What PL/SQL code
to execute as a job
Next_date Next execution date of the job
Interval Date expression to compute
next execute of date
No_parse Flag indicating whether to parse job PL/SQL at
time of submission (FALSE) or execution (TRUE)
When a job is submitted to the job queue, it runs automatically depending
on the NEXT_DATE and INTERVAL parameters. Jobs will not run automatically
when submitted on newly created databases that have not been bounced.
If a newly created database is bounced, then submitted jobs will run
automatically as expected. Also, when a database is started in restricted
mode, no jobs will be run.
Procedure REMOVE
Use the REMOVE procedure to delete or remove a job from the job queue.
DBMS_JOB.REMOVE(job IN BINARY_INTEGER);
Parameters
Description
Job
Job number. As long as the job exists, the number will remain the
same.
The only parameter required is the job number. A job that is currently
running is not interrupted. When the job completes, then the job
is removed. You can
only remove a job that you own. If you try to remove a job you
do not own, you receive a message stating the job does not exist.
Procedure CHANGE
To alter a job that has already been submitted to the job queue, use
the CHANGE procedure.
DBMS_JOB.CHANGE(job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2);
Parameters
Description
Job
Job number. As long as the job exists, the number will remain
the same.
What
The PL/SQL code you want executed. It is usually a call to a stored
procedure, which can have any number of parameters.
Use two single quotes around strings and a semicolon at the end of the
job definition.
Next_date
The date when the job will execute.
Interval
A function that calculates the next time the job is to execute.
The CHANGE procedure is used to alter more than one job characteristic
at once. If you specify NULL for the WHAT, NEXT_DATE, or INTERVAL
parameter, the current value for that parameter is not changed.
Procedure WHAT
This procedure is used to alter the definition of a job.
DBMS_JOB.WHAT(job IN BINARY_INTEGER,
what IN VARCHAR2);
Parameters
Description
Job
Job number. As long as the job exists, the number remains the same.
What
The PL/SQL code you want executed. It is usually a call to a stored
procedure, which can have any number of parameters. Use two single
quotes around strings and a semicolon at the end of the job definition.
When you execute the WHAT procedure, Oracle records your current environment,
which then becomes the new environment for the job.
Procedure NEXT_DATE
The NEXT_DATE procedure is used to alter the next date that Oracle
executes a job.
DBMS_JOB.NEXT_DATE(job IN BINARY_INTEGER,
next_date IN DATE);
Parameters
Description
Job
Job number. As long as the job exists, the number will remain the
same.
Next_date
The date when the job will execute next.
Procedure INTERVAL
The INTERVAL procedure is used to alter the interval between executions
for a specified job.
DBMS_JOB.INTERVAL(job IN BINARY_INTEGER,
interval IN VARCHAR2);
Parameters
Description
Job
Job number. As long as the job exists, the number will remain the
same.
Interval
A function that calculates the next time the job is to execute.
Procedure BROKEN
Use the BROKEN procedure to mark a job as broken or not broken.
DBMS_JOB.BROKEN(job IN BINARY_INTEGER,
broken IN BOOLEAN,
next_date IN DATE);
Parameters
Description
Job
Job number. As long as the job exists, the number remains the same.
Broken
Status of the job. If TRUE, the job is marked as broken. If
FALSE, the job is marked as not broken and is run at the time specified
by next_date.
Next_date
The date when the job will execute.
A job is marked either broken or not broken. Oracle7 does not attempt to run broken jobs. You can use the DBMS_JOB.RUN procedure to force a broken job to run. If that job runs successfully, the job is marked as not broken.
There are two ways a job can break:
1. Oracle7 fails to successfully execute the job after sixteen
attempts.
2. The job is marked as broken with the DBMS_JOB.BROKEN procedure.
Only jobs that you own may be marked as broken.
Procedure RUN
The DBMS_JOB.RUN procedure can be used to force a specific job to run
immediately.
DBMS_JOB.RUN(job IN BINARY_INTEGER);
Parameters
Description
Job
Job number. As long as the job exists, the number remains the same.
When executing a job with the RUN procedure, Oracle recomputes the next
execution date starting on the date the job is RUN. When you use
the RUN procedure, the job is executed in your current session. Your
session's packages are also reinitialized when the job is run.
Only jobs that you own can be executed using the RUN procedure.
An implicit COMMIT is performed when procedure RUN is executed. For
this reason, you cannot rollback once you execute a job using procedure
RUN.