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.
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.)
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;
DBMS_Scheduler and example of starting a script
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