Wednesday, January 29, 2014

DBMS_STATS error gathering fixed object stats ORA-20000 ORA-6512 insufficient privileges

While gathering fixed objects status using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS('ALL'), you can
encounter the following error;
ORA-20000 ORA-6512.. 
Error stack can be similar to following;
ORA-20000: Insufficient privileges to analyze an object in Fixed Ob
ORA-06512: at "SYS.DBMS_STATS"
ORA-06512: at "SYS.DBMS_STATS"
ORA-06512: at "SYS.DBMS_STATS"
ORA-06512: at line 1

Anyways, these errors are due to a error syntax.. 
Some Oracle documents say gather_fixed_objects_stats should be executed with 'ALL'  input,
but it seem they are wrong.. 
For example: E-Business Suite on Exadata Oracle Maximum Availability Architecture White Paper,October 2012
Because, DBMS_STATS.GATHER_FIXED_OBJECTS_STATS can take a parameter but it has
 to be an existingstatistics table created with DBMS_STATS.CREATE_STAT_TABLE.
(Reference Mos Document: 392364.1 )

For the solution; drop the 'ALL' argument and run the proc as follows;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();

No comments :

Post a Comment