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
exist
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:
sys@FUN10U09> ANALYZE TABLE “XTREME_EN”.”Financials” COMPUTE STATISTICS;
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
https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4774819970862237887::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,343355.1,1,1,1,helvetica
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!
