Schedule gather stats job in scheduler
Hello All,
I am trying to gather schema stats(all tables in specifc schemas) by scheduing a job in scheduler.
Working as expected on a specific table in a schema :
begin
dbms_scheduler.create_job(
job_name => 'GATHER_VENDOR_SCHEMA_STATS_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
filter_lst dbms_stats.objecttab := dbms_stats.objecttab();
BEGIN
filter_lst.extend(3);
filter_lst(3).ownname := ''DBO'';
filter_lst(3).objname := ''OI_ORGANIZATION'';
dbms_stats.gather_schema_stats(
OWNNAME => ''DBO'',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => ''FOR ALL INDEXED COLUMNS SIZE AUTO'',
DEGREE => DBMS_STATS.DEFAULT_DEGREE,
GRANULARITY => ''ALL'',
force => TRUE,