GATHER SCHEMA STATISTICS JOB FAILED

ISSUE: GATHER SCHEMA STATISTICS JOB FAILED SINCE 09-NOV-2010.






ERROR:
ORA-0000: normal, successful completion
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 20 degree = 8 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked
stats on table FND_CP_GSM_IPC_AQTBL is locked
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JE_FR_DAS_010***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JE_FR_DAS_010_NEW***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=AP.JG_ZZ_SYS_FORMATS_ALL_B***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***






FINDINGS:
There are duplicate rows on FND_HISTOGRAM_COLS table for 'JE_FR_DAS_010', 'JE_FR_DAS_010_NEW',
'JG_ZZ_SYS_FORMATS_ALL_B'  tables.Because of this problem, FND_STATS tries to gather histogram
information using wrong command and
it fails with ora-20001 errors.

select a.column_name, nvl(a.hsize,254) hsize from FND_HISTOGRAM_COLS a where
table_name = 'JE_FR_DAS_010' order by column_name;

COLUMN_NAME                         HSIZE
------------------------------ ----------
TYPE_ENREG                            254
TYPE_ENREG                            254

2 rows selected.


select a.column_name, nvl(a.hsize,254) hsize from FND_HISTOGRAM_COLS a where
table_name = 'JE_FR_DAS_010_NEW' order by column_name;

COLUMN_NAME                         HSIZE
------------------------------ ----------
TYPE_ENREG                            254
TYPE_ENREG                            254

2 rows selected.

select a.column_name, nvl(a.hsize,254) hsize from FND_HISTOGRAM_COLS a where
table_name = 'JG_ZZ_SYS_FORMATS_ALL_B' order by column_name;

COLUMN_NAME                         HSIZE
------------------------------ ----------
JGZZ_EFT_TYPE                         254
JGZZ_EFT_TYPE                         254

2 rows selected.








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
);



Refer: Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade [ID 781813.1]