Scheduling Jobs with Oracle 9i DBMS_JOB Package

Creating a new job that runs every day at 4:00am:

declare 
	l_job number; 
begin 
	dbms_job.submit(
		l_job, -- OUT; the job ID number that will be generated
		'schema_name.procedure_name;', -- IN; the name of the job you wish to run, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated
	); 
end; 

Regarding the interval, here are some examples:

-- Every 15 minutes starting from the minute/second of the previous execution
'sysdate+1/24/4'

-- Every hour, same minute/second as the previous execution
'sysdate+1/24'

-- Every hour, at the 15-minute mark
'trunc(sysdate, 'hh')+1/24+15/24/60'

-- Every hour, limited to between 9:00am and 5:00pm
'case when to_char(sysdate, ''hh24mi'') between ''0900'' and ''1700'' then sysdate+1/24 else null end'

-- Every 3 days, same hour/minute/second as the previous execution
'sysdate+3'

-- Every day at 5:00am
'trunc(sysdate)+1+5/24'

-- Every Monday at 5:00am
'next_day(trunc(sysdate), ''monday'')+5/24'

To see a list of existing jobs:

select * from dba_jobs;

Altering all properties of an existing job:

begin
	dbms_job.change(
		123, -- IN; job ID number
		'schema_name.procedure_name;', -- IN; the name of the job, aka. "what"
		trunc(sysdate)+1+4/24,  -- IN; the first time the job will be run after this change
		'trunc(sysdate)+1+4/24' -- IN; the interval the job will be repeated
	);
end;

Altering just the “what”:

begin
	dbms_job.what(
		123, -- IN; job ID number
		'schema_name.procedure_name;' -- IN; the name of the job, aka. "what"
	);
end;

These procedures allows you to make changes in a manner very similar to dbms_job.what illustrated above:

	.next_date
	.interval

Force a job to run:

begin
	dbms_job.run(123);
	-- ... where the "123" is the job's ID number
end;

Removing an existing job:

begin
	dbms_job.remove(123);
	-- ... where the "123" is the job's ID number
end;