Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade

Gather Schema Statistics" program reported following errors in request log files :

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

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***

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

 Action :

To Check which objects or tables are locked 

sql > SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED

         FROM DBA_TAB_STATISTICS

         WHERE STATTYPE_LOCKED IS NOT NULL;

TO Unlock all the tables in a schema at once :

sql> exec dbms_stats.unlock_schema_stats('schema_owner');

e.g : sql> exec dbms_stats.unlock_schema_stats('apps');

TO Unlock all Individual  tables in a schema at once 

sql> exec dbms_stats.unlock_schema_stats('table_owner','table_name');

e.g : sql > exec dbms_stats.unlock_schema_stats('AR','AR_REV_REC_QT');

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 a.column_name, nvl(a.hsize,254) hsize

from FND_HISTOGRAM_COLS a

where table_name = 'JE_BE_LINE_TYPE_MAP'

order by column_name;

COLUMN_NAME HSIZE

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

SOURCE 254

SOURCE 254

2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

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

  );