- Administration
- Structure
- Performance
- Backup
- Miscellaneous
- SQL*Net
|
|
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
/
|
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=>
)
|
|
