Sunday, November 22, 2015

ORA-20000: Unable to analyze TABLE ' ' , insufficient privileges or does not exist

When you want to gather stat of a table and oracle throw ORA-20000:

begin 
dbms_stats.gather_table_stats(
ownname=> 'your_username',
tabname=> 'jbm' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;

ERROR at line 1:
ORA-20000: Unable to analyze TABLE "your_username"."jbm", insufficient
privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 24270
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 2



SQL> grant analyze any to your_username;

SQL> exec dbms_stats.unlock_table_stats(ownname => 'your_username',tabname => 'jbm');

SQL> begin 
dbms_stats.gather_table_stats(
ownname=> 'your_username',
tabname=> 'jbm' ,
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;
/

PL/SQL procdure successfully completed.

5 comments: