Skip to content

Gather Schema Statistics fails with Ora-20001 errors in Oracle EBS

“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.

 

[Post Views: 46]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

Be First to Comment

Leave a Reply

Required fields are marked *