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

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

Go 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] 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 have been upgraded to 11G. Cause There is and reasons for that error message: 1) There is 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 both: 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 is both rows in histograms table, Fnd_stats creates following command to gather statistics on table ' Je_be_lin E_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 would work on 9i and 10G databases but it'll 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 the following SQL to identify. SQL would prompt for table name, with 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

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