I hope someone can help with a problem I'm having generating statistics in
Oracle 9i. This problem started suddenly with no known changes to the
database. The Oracle instance that this database is on also contains
another database which is not having this problem.

When executing the following:
exec dbms_stats.gather_schema_stats(ownname=>'ORACLE',
estimate_percent=>30, block_sample=>TRUE, cascade=>TRUE, method_opt=> 'for
all indexes for all indexed columns size auto');
I get this error:
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of
rows
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9616
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at line 1

If I execute an analyze on an individual table, I get the following error:
dbms_stats.gather_table_stats(ownname=> 'ORACLE', tabname=> 'table_x',
partname=> NULL);
ORA-20000: Cannot parse for clause: FOR ALL COLUMNS SIZE 1
ORA-06512: at sys.dbms_stats, line .....

If I execute an analyze on an individual index, it works correctly.
dbms_stats.gather_index_stats(ownname=> 'ORACLE', indname=> 'index_y',
partname=> NULL);

And if I issue an analyze without using the dbms_stats package, it works
correctly.
ANALYZE TABLE "ORACLE"."table_x" COMPUTE STATISTICS;

Thanks for your help.
Karen Coulter

Oracle LazyDBA home page