How to Create a Job using DBMS_SCHEDULER to execute a Shell Script every Thursday at 9:00 am 

Hey everyone,

Hope you’re doing good.

I was working for a client which would like to schedule a schell script every Thursday at 9:00 am to remove old traces.

Client had only one requirement: use DBMS_SCHEDULER instead Cron.

For this, first we need to create a SCHEDULE for every Thursday at 9:00 am:

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
repeat_interval => 'FREQ=WEEKLY;BYTIME=090000;BYDAY=THU',
schedule_name => 'EVERY_THURSDAY_9AM');
END;
/

As we are going to execute a shell script, we need to create a program through DBMS_SCHEDULER, pointing to shell script.

Let’s create the program and enable it:

BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'PRG_REMOVE_OLD_TRACES',
program_action => '/home/oracle/scripts/remove_old_traces.sh',
program_type => 'EXECUTABLE',
number_of_arguments => 0,
comments => 'Program to run remove_old_traces.sh',
enabled => FALSE);


DBMS_SCHEDULER.ENABLE(name=>'PRG_REMOVE_OLD_TRACES');

END;
/

As the last part, let’s create and activate the job which will use the program we created previously, the schedule we created previously, and also enable the full logging for this job:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_REMOVE_OLD_TRACES',
program_name => '"SYS"."PRG_REMOVE_OLD_TRACES"',
schedule_name => '"SYS"."EVERY_THURSDAY_9AM"',
enabled => FALSE,
auto_drop => TRUE,
comments => '',
job_style => 'REGULAR');




DBMS_SCHEDULER.SET_ATTRIBUTE( 
name => 'JOB_REMOVE_OLD_TRACES', 
attribute => 'store_output', value => TRUE);
DBMS_SCHEDULER.SET_ATTRIBUTE( 
name => 'JOB_REMOVE_OLD_TRACES', 
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FULL);




DBMS_SCHEDULER.enable(
name => 'JOB_REMOVE_OLD_TRACES');
END;
/

Hope it helps.

Peace,

Vinicius