ORA-20001 in R12 Gather schema statistics on 11g(FND_HISTOGRAM_COLS)

“Gather Schema Statistics” program reported the following errors in request log files :

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #4: ERROR: While GATHER_TABLE_STATS: object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

This occurred if there are duplicate or invalid columns in FND_HISTOGRAMS_COL

(1 ) There are duplicate rows on the FND_HISTOGRAM_COLS table for the JE_BE_LINE_TYPE_MAP table. Because of this problem, FND_STATS tries to gather histogram information using the wrong command and it fails with ORA-20001 errors.

The following SQL should return one row, not two:

SQL>select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

Since there are two rows in the histograms table, FND_STATS creates the following command to gather statistics on table ‘JE_BE_LINE_TYPE_MAP’ :

dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SOURCE SIZE 254 FOR
COLUMNS SOURCE SIZE 254');

The above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

(2) Column does not exist on the table but is still listed in the FND_HISTOGRAMS_COL table.

You can use the following SQL to identify. SQL will prompt for the table name, use table name from the errors. In the above examples, you can use FII_FIN_ITEM_HIERARCHIES.

select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='&TABLE_NAME'
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null;

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them. Remember to take a backup of the FND_HISTOGRAM_COLS table before deleting any data.
— identify duplicate rows

select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

— Use the above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and column_name = '&COLUMN_NAME'
and rownum=1;

— Use the following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name ='&TABLE_NAME'
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
);

Also Reads
ORA-00900 : Invalid Sql statement
ORA-03113
ora-29283: invalid file operation
ORA-29285: file write error
ORA-00054