Administration - Job Scheduler

 Administration - Job Schedule

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

/

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;

/

This will run a stored procedure called 'daily_data_clense' each day at 10pm.

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;

/

Schedule a 10g job

begin

dbms_sheduler.create_job(

job_name=>'andy.andy_job_test',

program_name=>'andy.job_test',

start_date=>

)

--stop a job

BEGIN

SYS.DBMS_SCHEDULER.STOP_JOB ( JOB_NAME =>'HEDW_DEV1.PRUN_MISD_BASE_DATA_LOAD', FORCE => TRUE );

END;

/