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 |