Schedule Job as below.
BEGIN DBMS_SCHEDULER.create_job ( job_name => 'GATHER_TABLE_STATS_EMP', job_type => 'PLSQL_BLOCK', job_action => 'begin dbms_stats.gather_table_stats(ownname =>''USERNAME'',tabname =>''EMP'',estimate_percent => 35,degree => 8,CASCADE=>TRUE,force => TRUE); END;', start_date => '29-NOV-11 11.00.00AM US/eastern', repeat_interval => 'FREQ=WEEKLY;INTERVAL=2;BYDAY=SAT;BYHOUR=11;BYMINUTE=0', end_date => NULL, enabled => TRUE, comments => 'GATHER STATS ON EMPTABLE'); END; / BEGIN DBMS_SCHEDULER.run_job (job_name => 'GATHER_TABLE_STATS_EMP', use_current_session => FALSE); END; /
You can define repeat intervals of jobs, windows or schedules using the Scheduler's calendar syntax.
SET SERVEROUTPUT ON;
ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MIS';
DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := to_timestamp_tz('29-NOV-11 09.00.00','DD-MON-YYYY HH24:MIS');
return_date_after := start_date;
FOR i IN 1..5 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=WEEKLY;INTERVAL=2;BYDAY=SAT;BYHOUR=11;BYMINUTE=0',start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
return_date_after := next_run_date;
END LOOP;
END;
/
You can change frequency as you like to Test.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.