ORA 20001
Following solution is extracted from article id
781813.1.
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.
Following SQL should have returned 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.
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
);
