DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Monday, February 2, 2015

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

This Error may occur while running Gather Schema Statistics after 11g Database Upgrade .

The Cause may be:

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:

select column_name, nvl(hsize,254) hsize
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

------------------------------ ----------

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;


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(*)
group by table_name, column_name
having count(*) > 1;

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

where table_name = '&TABLE_NAME'
and  column_name = '&COLUMN_NAME'
and rownum=1;

Now Run the Gather Schema Statistics, Your Error would've been fixed.

1 comment:

Sridevi K said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.