Patch Reviews - DB (MOSC)

MOSC Banner

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);

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center