PL/SQL Packages DBMS_JOB

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:

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


 
Copyright © 1999-2024 SS64.com
Some rights reserved