ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt

Summary :

Gather schema statistics for ALL schemas failed with the below error.

Error :

**SQL error and free**16-OCT-2016 14:19:21
ORACLE error 20001 in FDPSTP

Cause: FDPSTP failed due to ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt
ORA-06512: at "APPS.FND_STATS", line 2680
ORA-06512: at line 1
.

The SQL statement being executed at the16-OCT-2016 14:19:21
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

*** Please apply this fix in TEST instance before moving it to production.

Solution :

-- identify duplicate rows

select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

-- Use 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 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

  );

commit;

Reference :

Please read :

11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1