Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 3, 2003

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

By DatabaseJournal.com Staff

Ajay Gursahani, ajay.gursahani@mahindrabt.com

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);
job - Number of the job being run.

what - PL/SQL procedure to run.

next_date - Date of the next refresh.

interval - Date function evaluated immediately before the job starts running.

instance - When a job is submitted, specifies which instance can run the job. This defaults to NULL, which indicates that instance affinity is not changed.

force - If this is FALSE, then the specified instance must be running. Otherwise, the routine raises an exception. If this is TRUE, then any positive integer is acceptable as the job instance.


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);
job - Number of the job being run.

what - PL/SQL procedure to run.


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.


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date