ORA-20001: Invalid Column Name or Duplicate Columns/Column groups/expressions In method_opt

Symptoms:

Gather Schema Statistics" program reported following errors in request log files:

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

Statistics Errors:
stats on table FND_CP_GSM_OPP_AQTBL is locked
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP******
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 #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***
groups/expressions in method_opt***

Cause:

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

The following SQL should return one row, not two:
SQL> show user
USER is "APPS"

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

SQL>
Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :


Solution:                                                                                                                                         

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

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

TABLE_NAME                     COLUMN_NAME                      COUNT(*)
------------------------------ ------------------------------ ----------
JE_BE_LOGS                     DECLARATION_TYPE_CODE                   2
JE_FR_DAS_010                  TYPE_ENREG                              2
JE_FR_DAS_010_NEW              TYPE_ENREG                              2
JE_BE_LINE_TYPE_MAP            SOURCE                                  2
JE_BE_VAT_REP_RULES            SOURCE                                  2
JE_BE_VAT_REP_RULES            LINE_TYPE                               2
JE_BE_VAT_REP_RULES            VAT_REPORT_BOX                          2
JG_ZZ_SYS_FORMATS_ALL_B        JGZZ_EFT_TYPE                           2

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

SQL> delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;
Enter value for table_name: JE_BE_LOGS
old   2: where table_name = '&TABLE_NAME'
new   2: where table_name = 'JE_BE_LOGS'
Enter value for column_name: DECLARATION_TYPE_CODE
old   3: and  column_name = '&COLUMN_NAME'
new   3: and  column_name = 'DECLARATION_TYPE_CODE'

1 row deleted.

SQL> /
Enter value for table_name: JE_FR_DAS_010
old   2: where table_name = '&TABLE_NAME'
new   2: where table_name = 'JE_FR_DAS_010'
Enter value for column_name: TYPE_ENREG
old   3: and  column_name = '&COLUMN_NAME'
new   3: and  column_name = 'TYPE_ENREG'

1 row deleted.

SQL> /
Enter value for table_name: JE_FR_DAS_010_NEW
old   2: where table_name = '&TABLE_NAME'
new   2: where table_name = 'JE_FR_DAS_010_NEW'
Enter value for column_name: TYPE_ENREG
old   3: and  column_name = '&COLUMN_NAME'
new   3: and  column_name = 'TYPE_ENREG'

1 row deleted.

SQL> /

Repeat same till you delete all the duplicate rows and then commit.
SQL> select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;

no rows selected
SQL>
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

SQL>
SQL> commit;

Commit complete.
SQL>