Jobs

-- Drop job:
BEGIN
  DBMS_SCHEDULER.drop_job (
    job_name        => 'RefreshMatviews'
  );
END;
/


-- Create new job:
BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'RefreshMatviews',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE rc number(32); BEGIN rc:=pck_dwh.refresh(''ALL''); END;',
    start_date      => SYSTIMESTAMP,  -- run now (in order to see if the job works) ...
    repeat_interval => 'trunc(SYSTIMESTAMP) + 1 + 6/24', -- ... next run: tomorrow 6 o'clock in the morning
-- also possible:
--    start_date      => trunc(SYSTIMESTAMP) + 1 + 6/24, -- first run: tomorrow 6 o'clock in the morning
--    repeat_interval => 'freq=daily; interval=1', -- possible values; yearly, monthly, weekly, daily, hourly, minutely and secondly
    enabled         => TRUE,
    comments        => 'This Job is important.'  );
END;
/


-- Job Views:
select j.job_name, j.start_date, j.last_start_date, j.last_run_duration, j.next_run_date, j.run_count, j.enabled  
from DBA_SCHEDULER_JOBS j 
where job_name = 'REFRESHMATVIEWS'
;

select * from DBA_SCHEDULER_SCHEDULES;

select * from DBA_SCHEDULER_PROGRAMS;


-- Test job:
BEGIN
  DBMS_SCHEDULER.run_job (
    job_name        => 'DUMMYJOB4'
  );
END;
/






No comments:

Post a Comment