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;
BEGIN
   DBMS_JOB.SUBMIT(:jobno,
      'dbms_ddl.analyze_object(''TABLE'',
      ''dbceo'', ''accounts'',
      ''ESTIMATE'', NULL, 50);',
      SYSDATE, 'SYSDATE + 1');
   COMMIT;
END;
/
Statement processed.
PRINT jobno
JOBNO
----------
113245

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:

DBMS_JOB.REMOVE(113245);

 
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 :

SELECT JOB_NAME,state
    FROM DBA_SCHEDULER_JOBS;

JOB_NAME                       STATE
------------------------------ ---------------
AUTO_SPACE_ADVISOR_JOB         SCHEDULED
GATHER_STATS_JOB               SCHEDULED
FGR$AUTOPURGE_JOB              DISABLED
PURGE_LOG                      SCHEDULED

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 ;
 
JOB_NAME                       STATE
------------------------------ ---------------
AUTO_SPACE_ADVISOR_JOB         SCHEDULED
GATHER_STATS_JOB               DISABLED
FGR$AUTOPURGE_JOB              DISABLED
PURGE_LOG                      SCHEDULED