Oracle is the world's most robust and flexible database and Oracle provides a
great internal scheduler in the dbms_scheduler package.
There is an excellent overview of Oracle shell
scripting best practices in book "Oracle
Shell Scripting" by Jon Emmons, and see the book "Oracle
Job Scheduling" for an overview of using dbms_scheduler for executing
external shell scripts within Oracle.
Oracle's dbms_scheduler package allows you to "shell-out" to the OS
environment, executing shell scripts and bat files, directly from within Oracle.
In UNIX and Linux, it's easy to
run
an OS script from dbms_scheduler.
Also, note that you can embed OS commands directly inside PL/SQL.
Ken Naim published this working script which invokes a Windows bat file from
inside Oracle, using the dbms_scheduler syntax:
begin dbms_scheduler.drop_job (job_name=>'daily_backup');
end;
/
begin dbms_scheduler.drop_program(program_name => 'backup_database');
end;
/
begin dbms_scheduler.drop_schedule(schedule_name =>
'daily_at_4am_except_monday'); end;
/
begin
dbms_scheduler.create_schedule(
schedule_name =>
'daily_at_4am_except_monday',
repeat_interval => 'FREQ=DAILY; INTERVAL=1;
BYDAY=TUE,WED,THU,FRI,SAT,SUN; BYHOUR=4',
comments
=> 'schedule to run daily at 4am except on mondays');
dbms_scheduler.create_program
( program_name => 'backup_database',
program_type => 'EXECUTABLE',
program_action => 'd:\oracle\product\admin\dw\scripts\backup_dw.bat
>
nul',
enabled
=> TRUE,
comments
=> 'Backup dw database using rman and then backup rman
database via hot backup.'
);
dbms_scheduler.create_job (
job_name=>'daily_backup',
program_name =>'backup_database',
schedule_name=>
'DAILY_AT_4AM_EXCEPT_MONDAY',
enabled => true,
comments =>
'backs up the dw and rman databases daily at 4am
except on for mondays.'
);
end;
/
select * from dba_scheduler_jobs
|
|
and include the URL for the page.
