DBMS_JOBS

Creating Job:
DECLARE


v_job_nbr NUMBER;

v_job_txt VARCHAR2(2000) := 'CLOSR_SP_DATA_COPY;';

v_job_comment_txt VARCHAR2(2000) := 'This Procedure will refresh the last two days of data from a couple of the CLOSR


tables. Questions contact Dan Waltz';

BEGIN



dbms_scheduler.create_job(

job_name => 'CLOSR_DATA_REFRESH',

job_type => 'plsql_block',

job_action => v_job_txt,

start_date => SYSDATE,

repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=6; BYMINUTE=0; BYSECOND=0',

end_date => NULL,

enabled => TRUE,

comments => v_job_comment_txt

);



END;

*****************************************************************
--Select a job from the table


SELECT job_name, owner, schedule_owner, start_date, repeat_interval, job_action, comments

FROM DBA_SCHEDULER_JOBS sj

where owner in ('CMTS_RPTS', 'SUPPORT_SERVICES')

and job_name = 'ORARPT_PROBLEM_REPORTS_CAPTURE';





--Get job details from the table

select *

from dba_scheduler_job_run_details

where job_name = 'ORARPT_DAILY_CUSTOM' order by log_date desc;



--set attribute

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE (

name => 'ORARPT_PROBLEM_REPORTS_CAPTURE',

attribute => 'repeat_interval',

value => 'FREQ=DAILY');

END;



--Drop a job from the table

BEGIN

dbms_scheduler.drop_job(

job_name => 'ORARPT_PROBLEM_REPORTS_CAPTURE'

);

END;



select *

from dba_scheduler_jobs

where job_name = 'CLOSR_DATA_REFRESH';



select *

from support_services.cop_job_watch

where job_nm = 'CLOSR_DATA_REFRESH';



select *

from dba_scheduler_job_run_details

where job_name = 'CLOSR_DATA_REFRESH';



select *

from dba_scheduler_job_run_details

where status = 'FAILED'