Viewing and Managing Job Queues in Oracle 9i - Page 3

January 3, 2003

DBMS_JOB.CHANGE

Alters a specified job that has already been submitted to the job queue. The job description, the time at which the job will be run, the interval between executions of the job, and the instance and force of a job can all be altered.

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

Example:

EXECUTE DBMS_JOB.CHANGE(1,null,sysdate+3,null); 

Changes the next_date of job number 1 from 08/11/2002 to (system date+3).
If system date is 15/12/2002, then the next_date will be 18/12/2002.

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL003;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.

DBMS_JOB.WHAT

The definition of a job can be altered by calling the DBMS_JOB.WHAT procedure.

DBMS_JOB.CHANGE ( 
   job    IN  BINARY_INTEGER,
   what   IN  VARCHAR2);

Example:

The following example changes the definition for job 3. The procedure DEL003 is replaced by procedure DEL006.

execute DBMS_JOB.WHAT(3,'HOUSEKEEP.DEL006;');

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;


JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL006;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.