“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***
There can be two possible reasons for this issue:
REASON 1: DUPLICATE ROWS IN FND_HISTOGRAM_COLS
There can be duplicate rows in 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 an it fails with ora-20001 errors.
Run following SQL should returned one row and not two.
select column_name, nvl(hsize,254) hsize from FND_HISTOGRAM_COLS where table_name = 'JE_BE_LINE_TYPE_MAP' order by column_name;
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.
REASON 2: MISSING COLUMN IN 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;
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
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 );
Test by running the GSS again.
Latest posts by Brijesh Gogia (see all)
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020