DBMS_STATS Job sometimes fails with ORA-20000 Insufficient privileges to analyze an object in Schema
we have a statistics job, owned by SYS, that runs daily and executes following PL/SQL block:
begin
dbms_stats.set_schema_prefs(ownname=>'XXX', pname=>'PUBLISH', pvalue=>'FALSE');
dbms_stats.gather_schema_stats(ownname=>'XXX', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY', estimate_percent=>dbms_stats.AUTO_SAMPLE_SIZE, cascade=>true, no_invalidate=>true, degree=> 2);
DBMS_STATS.PUBLISH_PENDING_STATS(ownname=>'XXX', tabname=>null, no_invalidate=>true);
dbms_stats.set_schema_prefs(ownname=>'XXX', pname=>'PUBLISH', pvalue=>'TRUE');
dbms_stats.set_schema_prefs(ownname=>'YYY', pname=>'PUBLISH', pvalue=>'FALSE');
dbms_stats.gather_schema_stats(ownname=>'YYY', method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY', estimate_percent=>dbms_stats.AUTO_SAMPLE_SIZE, cascade=>true, no_invalidate=>true, degree=> 2);