I will explain DBMS_JOB and dbms_job.submit in Oracle in this post.
DBMS_JOB Submit in Oracle
You can read the following post to learn more details about DBMS_JOB.
Oracle has the DBMS_JOB package which schedules and manages jobs in the job queue and it has been superseded by the DBMS_SCHEDULER package.
If you submit a job to the job queue, You can use the dbms_job.submit procedure .
You use the following syntax for the dbms_job.submit.
DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE DEFAULT SYSDATE, interval IN VARCHAR2 DEFAULT 'NULL', no_parse IN BOOLEAN DEFAULT FALSE, instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE, force IN BOOLEAN DEFAULT FALSE);
DBMS_JOB.SUBMIT – Gather Schema Stats job
Gather Schema Stats job with DBMS_JOB are as follows.
SET SERVEROUTPUT ON DECLARE l_job NUMBER; BEGIN SELECT MAX (job) + 1 INTO l_job FROM dba_jobs; DBMS_JOB.submit(l_job, 'BEGIN DBMS_STATS.gather_schema_stats(''MSD'',estimate_percent => dbms_stats.auto_sample_size, degree=>8 ); END;', trunc(next_day(SYSDATE,'SUNDAY'))+11/24, 'TRUNC (SYSDATE+7)+11/24'); COMMIT; DBMS_OUTPUT.put_line('Job: ' || l_job); END; / dbms_job.submit( what=>'statspack.sql;', next_date=>sysdate+1/24, -- start next hour interval=>'sysdate+3/24'); -- Run every 3 hour
If you assign a particular instance to execute a job, use the following syntax:
DBMS_JOB.INSTANCE( JOB IN BINARY_INTEGER, instance IN BINARY_INTEGER, force IN BOOLEAN DEFAULT FALSE);
By The default, The force parameter for DBMS_JOB.RUN is FALSE.
If You set the Force to TRUE, instance affinity is irrelevant for running jobs in the foreground process. If force is FALSE, the job can run in the foreground only in the specified instance.
Oracle displays error ORA-23428 if force is FALSE and the connected instance is the incorrect instance.
DBMS_JOB.RUN( job IN BINARY_INTEGER, force IN BOOLEAN DEFAULT FALSE);
You can set the broken flag using this procedure.
Broken jobs are never run in Oracle.
DBMS_JOB.BROKEN ( job IN BINARY_INTEGER, broken IN BOOLEAN, next_date IN DATE DEFAULT SYSDATE);
You can change any of the fields a user can set in a job using this procedure.
DBMS_JOB.CHANGE ( job IN BINARY_INTEGER, what IN VARCHAR2, next_date IN DATE, interval IN VARCHAR2, instance IN BINARY_INTEGER DEFAULT NULL, force IN BOOLEAN DEFAULT FALSE);
BEGIN DBMS_JOB.CHANGE(1453, null, null, 'sysdate+1'); COMMIT; END;
You can changee job instance affinity using this procedure.
DBMS_JOB.INSTANCE ( job IN BINARY_INTEGER, instance IN BINARY_INTEGER, force IN BOOLEAN DEFAULT FALSE);
You can change how often a job runs using this procedure.
DBMS_JOB.INTERVAL ( job IN BINARY_INTEGER, interval IN VARCHAR2);
You can change when an existing job next runs using this procedure.
DBMS_JOB.NEXT_DATE ( job IN BINARY_INTEGER, next_date IN DATE);
This procedure removes an existing job from the job queue. This currently does not stop a running job.
DBMS_JOB.REMOVE ( job IN BINARY_INTEGER );
BEGIN DBMS_JOB.REMOVE(1453); COMMIT; END;
1,611 views last month, 8 views today