To schedule and manage jobs in the job queue, use the procedures in the
package. There are no database privileges associated with using job
queues. Any user who can execute the job queue procedures can use the
job queue. The following are procedures of the
DBMS_JOB package. They are described below.
SUBMIT : Submits a job to the job queue
REMOVE : Removes a specified job from the job queue.
: lters a specified job that has already been submitted to the job
queue. You can alter the job description, the time at which the job will
be run, or the interval between executions of the job.
WHAT : Alters the job description for a specified job.
NEXT_DATE : Alters the next execution time for a specified job.
INTERVAL : Alters the interval between executions for a specified job.
BROKEN : Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it.
RUN : Forces a specified job to run.
For example, consider the following statements that submits a new job to the job queue. The job calls the procedure
DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table
dbceo.accounts. The statistics are based on a sample of half the rows of the
accounts table. The job is run every 24 hours.
VARIABLE jobno NUMBER;
''ESTIMATE'', NULL, 50);',
SYSDATE, 'SYSDATE + 1');
Removing a Job from the Job Queue
To remove a job from the job queue, use the
REMOVE procedure in the
The following statement removes job number 14144 from the job queue:
DBA view describes all the jobs in the database.
ALL view describes all jobs that are accessible to the current user.
USER view describes all jobs owned by the current user.
Date Expression - Evaluation
'SYSDATE + 7' - Exactly seven days from the last execution
'SYSDATE + 1/48' - Every half hour
'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' - Every Monday at 3PM
'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3),''THURSDAY'')' - First Thursday of each quarter
Jnnn processes execute jobs. To execute a job, the process creates a session to run the job.When a Jnnn process
runs a job, When a Jnnn process runs a job, the job is run in the same environment in which it was submitted and
with the owner's default privileges. The owner must be explicitly granted the necessary object privileges for
all objects referenced within the job definition.
When you force a job to run using the procedure
DBMS_JOB.RUN, the job is run by your user process and with your
default privileges only. Privileges granted to you through roles are unavailable.
You must be explicitly granted the necessary object privileges for all objects referenced within the job definition.
Stats gathering jobs :
Disable Stats gathering jobs :
SQL> exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
PL/SQL procedure successfully completed.
SQL> SELECT JOB_NAME,state
FROM DBA_SCHEDULER_JOBS ;