To schedule and manage jobs in the job queue, use the procedures in the DBMS_JOB 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.
CHANGE : Alters 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 DBMS_JOB package.
The following statement removes job number 14144 from the job queue:
Viewing Job Queue Information
DBA_JOBS : DBA view describes all the jobs in the database.
ALL_JOBS : ALL view describes all jobs that are accessible to the current user.
USER_JOBS : USER view describes all jobs owned by the current user.
Job Execution Interval
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
How Jobs Execute
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 ;