Posted by: Pavel Ermakov | July 29, 2011

exec DBMS_STATS.GATHER_SCHEMA_STATS -> ORA-20000: Unable to analyze insufficient privileges or does not exist

Hi

Recently, I had this error when automated statistics job was executed in one of my DB servers:

Schema to gather stats: USER
begin
*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 15175
ORA-06512: at "SYS.DBMS_STATS", line 15535
ORA-06512: at "SYS.DBMS_STATS", line 15608
ORA-06512: at "SYS.DBMS_STATS", line 15567
ORA-06512: at line 2

As you can see output give you nothing at all. Ok, I logged in using SYS schema and before ran this script(that using automated job) I enabled 10046 trace. In the trace file I found this:

DBMS_STATS:
ORA-20000: Unable to analyze INDEX "USER"."SYS_IL0000052262C00004$$" SYS_IL_P43, insufficient privileges or does not exist
EXEC #2:c=616038,e=18633079,p=17423,cr=20712,cu=985,mis=0,r=0,dep=0,og=1,tim=1281178233994225
ERROR #2:err=20000 tim=2734552089

This is LOB index and he doesn’t exist(I asked user_indexes). Ok. But why oracle trying analyze index that doesn’t exist? I did following steps to fix this issue:


1. begin
dbms_stats.delete_fixed_objects_stats();
end;
/
2. begin
dbms_stats.gather_fixed_objects_stats();
end;
/
3. exec dbms_stats.gather_table_stats(user,'TABLE',cascade=>true); (table that not existing index corresponds earlier)
4. @/gather_stats.sql; (which perform gather_schema_stats)

My version is:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options. (2 nodes RAC).

HTH

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: