Gather Schema Statistics fails with Ora-20001 errors after Database upgrade (11g to 12C)

"Gather Schema Statistics" program reported 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***

Above problem started after migrating database to 11g to 12c

There are 2 reason for this error message

1. There are duplicate rows on FND_HISTOGRAM_COLS table

2. Column doesn't exist in table but it is still listed in FND_HISTOGRAM_COLS table 

Solution

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the apps user.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

Identify duplicate rows using the following query:

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;