ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
CAUSE: Due to duplicate rows/obsolete rows in FND_HISTOGRAMS_COLS table, FND_STATS tries to gather histogram information using a wrong commands and throws an ORA_2001 error.
1. The below sql to identify duplicate rows in FND_HISTOGRAM_COLS table:
select column_name from FND_HISTOGRAM_COLS
where table_name = ‘&TABLE_NAME’;
COLUMN_NAME
———————–
COL1
COL1
2. The below sql to identify obsolete rows in FND_HISTOGRAM_COLS table:
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;
Resolution
————–
Find out all duplicate rows and/or obsolete rows in FND_HISTOGRAM_COLS, login as applsys user and delete one of them.
Note: take backup of FND_HISTOGRAM_COLS table, before deleting any data.
— identify dupicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) >1;
— use the above results in the following sql to delete duplicates
delete from FND_HISTOGRAM_COLS
where table_name = ‘&TABLE_NAME’
and column_name = ‘&COLUMN_NAME’
and rownum = 1;