Pages

▼

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.
at

1 comment:

  1. UnknownMarch 01, 2013 5:28 AM

    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

    ReplyDelete