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

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

To Bottom

---

In this Document Symptoms Changes Cause Solution APPLIES TO: Oracle Applications Technology Stack - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1] Oracle EBS Applications Performance - Version 12.1.1 to 12.1.1 [Release 12.1] Oracle Applications DBA - Version 11.5.10.2 to 11.5.10.2 [Release 11.5.10] Information in this document applies to any platform. Oracle Server Enterprise Edition - Version: 11.1.0.6 to 11.1.0.8 SYMPTOMS "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*** 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*** CHANGES Problem started after database has been upgraded to 11G. CAUSE There are two reasons for that error message: 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>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 histograms table, FND_STATS creates 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');  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 still listed in FND_HISTOGRAMS_COL table. You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In 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; 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. -- 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;