DBA Sensation

March 10, 2009

dbms_stats has error when gather statistics on case sensitive objects

Filed under: [System Performance tuning] — Tags: , , , , — zhefeng @ 3:33 pm

i was trying to use dbms_stats gather the statistics for a table named: “XTREME_EN”.”Financials”, but got this error:

sys@FUN10U09> exec dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname
=> ‘Financials’);
BEGIN dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname => ‘Financials’); END;

ERROR at line 1:
ORA-20000: Unable to analyze TABLE “XTREME_EN”.”FINANCIALS”, insufficient privil
eges or does not
ORA-06512: at “SYS.DBMS_STATS”, line 13427
ORA-06512: at “SYS.DBMS_STATS”, line 13457
ORA-06512: at line 1

However, if i use traditional analyze command, it works:


Table analyzed.

Is that because dbms_stats stupider than analyze? Of course not, Oracle is always encouraging you to use “dbms_stats” instead of using “analyze”.

After search on the metalink, i found this doc: “DBMS_STATS Reports ORA-20000 and ORA-06512 On Case Sensitive Object Names”  Doc ID: 343355.1


According to this doc, we have to mention the object name in double quotes.This is very similar to how SQL supports mixed cases.

For our case:

sys@FUN10U09> exec dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname
=> ‘”Financials”‘);

PL/SQL procedure successfully completed.

Works perfectly!


Create a free website or blog at WordPress.com.