Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, March 18, 2012

ORA-20000 on executing DBMS_STATS.GATHER_TABLE_STATS on table


Problem:
DBMS_STATS.GATHER_TABLE_STATS('HOME','T_temp')

ORA-20000: Unable to analyze TABLE "HOME"."T_TEMP", insufficient
privileges or does not exist


Reason
The table "T_temp" is created in lower case . DBMS_STATS converts the table name to upper case - "T_TEMP" and hence the problem.

Solution:

To workaround this problem the tablename has to be included in an extra pair of double quotes as shown below :-
exec DBMS_STATS.GATHER_TABLE_STATS('HOME','"T_temp"');


Additionally using DBMS_STATS.GATHER_DICTIONARY_STATS does not encounter this issue.

1 comment:

Vasu Kamireddi said...

If you are calling DBMS_STATS as part of a stored procedure, then try adding invoker rights.

Something like this:

CREATE OR REPLACE procedure ...(..
) AUTHID current_user AS
...

HTH
Vasu Kamireddi