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.

 

Syntax

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.

 

Syntax

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.

Syntax

DBMS_JOB.INSTANCE ( 
   job        IN BINARY_INTEGER,
   instance   IN BINARY_INTEGER,
   force      IN BOOLEAN DEFAULT FALSE);



DBMS_JOB.INTERVAL Procedure

You can change how often a job runs using this procedure.

Syntax

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




DBMS_JOB.NEXT_DATE Procedure

You can change when an existing job next runs using this procedure.

Syntax

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




DBMS_JOB.REMOVE Procedure

This procedure removes an existing job from the job queue. This currently does not stop a running job.

 

Syntax

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 )

 

 1,298 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmet[email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply