Oracle 12c schema stats gather job
This post is more or less for self reference. Mainly in the hope of avoiding the struggle with ', " and ' ' during the job_action definition. :) So the lesson i always forget, when defining stats jobs never try to define the job action inside DBMS_SCHEDULER.CREATE_JOB, insted create a procedure first and then create stored procedure job type. Create the job based on stats parameters required, e.g. - create or replace procedure sunday_stats is BEGIN DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT',method_opt=>'FOR ALL COLUMNS SIZE REPEAT',degree=>1,granularity=>'AUTO',cascade=>true); END; / Create the job using the above procedure with required frequency, BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'scott_stat_sunday' ,start_date => SYSTIMESTAMP ,repeat_interval => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=11;BYMINUTE=21;BYSECOND=0' ,end_date => NULL ...