ORA 20001 Gather Schema Statistics fails

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

  );