Schedule and manage jobs in the job queue
Subprocedures:
SUBMIT Submit a new job to the job queue.
REMOVE Remove job from the job queue.
CHANGE Alter any of the user-definable parameters.
WHAT Alter the job description.
NEXT_DATE Alter the next execution time for a job.
INSTANCE Assign a job to be run by an instance.
INTERVAL Alter the interval between job executions.
BROKEN Disable job execution.
RUN Force a job to run.
USER_EXPORT Recreate a given job for export
and optionally set instance affinity.
Examples
Create a DBMS_STATS.gather_schema_stats job that runs daily at 11pm (23:00)
Set SERVEROUT ON
Set pagesize 200 DECLARE jobno NUMBER; BEGIN DBMS_JOB.submit (job => jobno, what => 'DBMS_STATS.gather_schema_stats(ownname => ''scott'', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);', next_date => trunc(sysdate)+23/24, interval => 'SYSDATE + 1', no_parse => TRUE ); DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno)); COMMIT; END; /
Note, the above will create a new job in the schema of the user you are logged in as (typically the schema owner rather than SYSTEM)
After running the above you can see the job created with the following:
SELECT 'Job:'|| job, WHAT, 'Next:'|| to_char(NEXT_DATE,'dd-Mon-yyyy HH24:MI'), ' Last:'|| to_char(LAST_DATE,'dd-Mon-yyyy HH24:MI'), ' Broken:'|| BROKEN FROM dba_jobs;
To remove the job run a command like this (where 25 is the job no.)
EXECUTE DBMS_JOB.REMOVE(25);
Related:
OraFAQ - DBMS_JOB examples - Examples and a function that use Oracle date arithmetic to make dbms_job.submit fully flexibile.
Alternatively DBMS_Schedule has some pre-baked and customisable (Daily,weekly,monthly) Schedules.
DBA_JOBS USER_JOBS DBA_JOBS_RUNNING
