DBMS_JOB and dbms_job.submit in Oracle

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.

How to List and Monitor DBMS Jobs and Scheduler Jobs in Oracle Database

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.

DBMS_JOB.SUBMIT

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

DBMS_JOB.INSTANCE

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);

DBMS_JOB.RUN

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);

EXECUTE DBMS_JOB.RUN(1453);

DBMS_JOB.BROKEN Procedure

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);

DBMS_JOB.CHANGE Procedure

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; 

DBMS_JOB.INSTANCE Procedure

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);

DBMS_JOB.INTERVAL ( 
   job       IN  BINARY_INTEGER,
   interval  IN  VARCHAR2);

DBMS_JOB.NEXT_DATE ( 
   job       IN  BINARY_INTEGER,
   next_date IN  DATE);

DBMS_JOB.REMOVE ( 
   job       IN  BINARY_INTEGER );

BEGIN
   DBMS_JOB.REMOVE(1453);
   COMMIT;
END;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )