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; /
The script above will create a new job in the schema of the current user (typically this will be the schema owner rather than SYSTEM)
To view the job created:
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);
A PL/SQL script to remove all oracle jobs for the current user:
declare
job user_jobs.job%TYPE;
CURSOR c IS
select job from user_jobs;
begin
OPEN c;
LOOP
fetch c into job;
exit when c%NOTFOUND;
dbms_output.put_line('Removing job: '||job);
dbms_job.remove(job);
END LOOP;
CLOSE c;
commit;
end;
Related:
OraFAQ - DBMS_JOB examples - Examples and a function that use Oracle date arithmetic to make dbms_job.submit fully flexibile.
Alternatively DBMS_Scheduler (in 10g +) has some pre-baked and customisable (Daily,weekly,monthly) Schedules and also allows executables to be run.
DBA_JOBS USER_JOBS DBA_JOBS_RUNNING