edba: Scheduler , Program and Jobs

How to create Job using DBMS_SCHEDULER :

A job is the combination of a schedule and a program, along with any additional arguments required by the program

Create a procedure.

Create a schedule.

This procedure can be called in a program or program can also have a PL/SQL block like DBMS_STATS.GATHER_TABLE_STATS.

This program can be called in a create job along with the created schedule or create job can also have a PL/SQL block like DBMS_STATS.GATHER_TABLE_STATS.

You can also create a job (call a program) based on an event condition e.g file arrival

Privileges required :

MANAGE SCHEDULER : Privilege required for administering the Scheduler

GRANT SCHEDULER_ADMIN TO username;
GRANT MANAGE SCHEDULER TO adam;

Views :

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

SELECT JOB_NAME, STATUS, ERROR# FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME STATUS ERROR#
-------- -------------- ------
MY_JOB1 FAILURE 20000

Create scheduler/Job to insert database size every month

CREATE TABLE <schema_name>.db_size
(
"INSERT_DATE" DATE,
"INSTANCE_NAME" VARCHAR2(10 BYTE),
"TOTAL_SIZE" NUMBER,
"DATAFILE_SIZE" NUMBER
)

Create or replace procedure dbgrowth
as
Begin
insert into <schema_name>.db_size
select sysdate,instance_name,
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "ToTal Size in GB" ,
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) df_size
from
v$instance;
End;
/

grant insert,update,delete on <schema_name>.db_size to <schema_name>;

grant select on v_$instance to <schema_name>;

grant select on v_$controlfile to <schema_name>;

grant select on dba_data_files to <schema_name>;

grant select on dba_temp_files to <schema_name>;

grant select on sys.v_$log to <schema_name>;

-- Creating a Stored Procedure with Arguments.
DBMS_SCHEDULER.create_program (
program_name => 'DBGROWTH_STORED_PROCEDURE_PROG',
program_type => 'STORED_PROCEDURE',
program_action => '<schema_name>.DBGROWTH',
number_of_arguments => 0,
enabled => FALSE,
comments => 'Program to gather DB GROWTH using a stored procedure.');

BEGIN
DBMS_SCHEDULER.create_schedule (
schedule_name => 'LASTFRIDAY_MONTHLY_SCHEDULE',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY; BYDAY=-1FRI',
end_date => NULL,
comments => 'Repeats monthly, on last Friday, for ever.');
END;

================== Three ways to implement ==================

DBMS_SCHEDULER.create_job (
job_name => 'DBGROWTH_JOB',
program_name => 'DBGROWTH_STORED_PROCEDURE_PROG',
schedule_name => 'LASTFRIDAY_MONTHLY_SCHEDULE',
enabled => TRUE,
comments => 'Job defined by an existing program and schedule.');

DBMS_SCHEDULER.create_job (
job_name => 'DBGROWTH_JOB',
program_name => 'DBGROWTH_STORED_PROCEDURE_PROG',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY; BYDAY=-1FRI',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined by existing program and inline schedule.');

Begin
DBMS_SCHEDULER.create_job (
job_name => 'DBGROWTH_JOB',
job_type => 'STORED_PROCEDURE',
job_action => '<schema_name>.DBGROWTH',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY; BYDAY=-1FRI',
end_date => NULL,
enabled => TRUE,
comments => 'Job to gather DB GROWTH on last Friday of every month.');
End;
/

Pages

Scheduler , Program and Jobs