DBMS SCHEDULER

From Robs_Wiki
Jump to: navigation, search

Create a scheduler job

See a usefull example below:

spool create_test_jobs.log
-- create the procedures to be called in the job
--
create or replace procedure robla.showme_false
as
begin
   raise NO_DATA_FOUND;
end;
/

create or replace procedure robla.showme_good
as
begin
   null;
end;
/

-- create the jobs, calling the procedures
--

exec dbms_scheduler.drop_job(job_name => 'robla.JOB_SHOWME_GOOD');

BEGIN
   dbms_scheduler.create_job(
   JOB_NAME => 'robla.JOB_SHOWME_GOOD',
   job_type => 'PLSQL_BLOCK',
   repeat_interval => 'FREQ=hourly;byminute=27',
   start_date => sysdate,
   enabled    =>  TRUE,
   comments   => 'test a good job',
   job_action=>'BEGIN robla.showme_good; END;'
);
END;
/

exec dbms_scheduler.drop_job(job_name => 'robla.JOB_SHOWME_FALSE');

BEGIN
   dbms_scheduler.create_job(
   JOB_NAME => 'robla.JOB_SHOWME_FALSE',
   job_type => 'PLSQL_BLOCK',
   repeat_interval => 'FREQ=hourly;byminute=27',
   start_date => sysdate,
   enabled    =>  TRUE,
   comments   => 'test a false job',
   job_action=>'BEGIN robla.showme_false; END;'
);
END;
/

-- set the email attribute
--
execute dbms_scheduler.set_scheduler_attribute('email_sender','rob.lasonder@sample.org');

-- add the email notifications
--
BEGIN
   dbms_scheduler.add_job_email_notification(
   job_name=>'robla.showme_good',
   recipients=>'rob.lasonder@sample.org',
   sender=>'oracle@server.sample.org',
   events=>'SUCCESS,'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR'
);
END;

BEGIN
   dbms_scheduler.add_job_email_notification(
   job_name=>'robla.showme_error',
   recipients=>'rob.lasonder@sample.org',
   sender=>'oracle@server.sample.org',
   events=>'SUCCESS,'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR'
);
END;
/

Execute a scheduler job manually

You can also run a scheduled job manually:

SQL> EXEC dbms_scheduler.run_job('PRDUSER.GG_DELETE_CUSTOM_HEARTBEAT');

PL/SQL procedure successfully completed.

Drop a scheduler job

declare
   l_job_exists number;
begin
   select count(*) into l_job_exists
     from user_scheduler_jobs
    where job_name = '&&JOB_NAME';

   if l_job_exists = 1 then
      dbms_scheduler.drop_job(job_name => '&&JOB_NAME');
   end if;
end;

View current status of jobs

This query shows every jobs, whether it is enabled or disabled and last and next rundate information. best run it from TOAD.

col owner format a20
col job_name format a30
col program_name format a30
col comments format a60
set linesize 200
select owner, job_name, state, program_name, to_char(last_start_date,'DD-MON-YYYY HH24:MI:SS') last_start_date, 
to_char(next_run_date,'DD-MON-YYYY HH24:MI:SS') next_run_date, comments 
from DBA_SCHEDULER_JOBS order by state desc;

View jobs that are currently running

SQL> SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

no rows selected

View history of job runs per run

col job_name format a40
col cpu_used format a20
col run_duration format a20
select job_name,status,to_char(actual_start_date,'DD-MON-YYYY HH24:MI:SS') actual_start_date, run_duration, cpu_used 
from dba_scheduler_job_run_details
where job_name = '&job_name'
order by actual_start_date desc;

Sample output:

JOB_NAME                     STATUS                         ACTUAL_START_DATE          RUN_DURATION         CPU_USED
---------------------------- ------------------------------ -------------------------- -------------------- --------
PLAN_TBL_MAINTENANCE         SUCCEEDED                      09-NOV-2018 19:27:48       +000 00:00:00        +000 00:00:00.00
ORA$AT_SA_SPC_SY_2026        SUCCEEDED                      04-DEC-2017 22:00:08       +000 00:35:30        +000 00:20:49.15
. . .