Administration - Job Scheduler

List scheduled jobs

set lines 100 pages 999
col schema_user format a15
col fails format 999
select job
, schema_user
, to_char(last_date, 'hh24:mi dd/mm/yy') last_run
, to_char(next_date, 'hh24:mi dd/mm/yy') next_run
, failures fails
, broken
, substr(what, 1, 15) what
from dba_jobs
order by 4
/
Output:
            JOB SCHEMA_USER     LAST_RUN       NEXT_RUN       FAILS B
--------------- --------------- -------------- -------------- ----- -
WHAT
---------------------------------------------
            642 APPS                           00:00 01/01/00    16 Y
begin
execute
            225 APPS            22:45 21/01/11 02:45 22/01/11     0 N
declare errbuf
            104 APPS            18:41 21/01/11 18:41 22/01/11     0 N
FND_SVC_COMPONE

3 rows selected.

Submit a job

dbms_job.submit('<code>', <start time>, <repeat time>, TRUE);

For example:

declare
  job_id  number;
begin
dbms_job.submit(
 job_id
, 'andy.daily_data_clense'
, trunc(sysdate+1)+22/24
, 'sysdate+1'
, true);
end;
/

Remove a job

You need to be connected as the user who owns the job

exec dbms_job.remove(<job number>);

Reset a broken job

You need to be connected as the user who owns the job

exec dbms_job.broken(<job number>, FALSE);

Add a program to the 10g scheduler (os program)

begin
dbms_scheduler.create_program(
program_name=>'ANDY.job_test',
program_action=>'/home/oracle/andyb/job_test.sh',
program_type=>'EXECUTABLE',
comments=>'test job',
enabled=>TRUE);
end;
/

Add a program to the 10g scheduler (stored procedure)

begin
dbms_scheduler.create_program(
program_name=>'ANDY.job_test',
program_action=>'andy.job_test',
program_type=>'STORED_PROCEDURE',
comments=>'test program',
enabled=>TRUE);
end;
/