Error “ORA-0000: normal, successful completion” during Gather Schema Statistics for AP Schema

ISSUE:
———
When
attempting to run the concurrent program Gather Schema Statistics for AP schema
the following error occurs.
ERROR
———-
ORA-0000:
normal, successful completion
+—————————————————————————+
Start of
log messages from FND_FILE
+—————————————————————————+
In
GATHER_SCHEMA_STATS , schema_name= AP percent= 10 degree = 8 internal_flag=
NOBACKUP
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***
+—————————————————————————+
End of log
messages from FND_FILE
+—————————————————————————+
SOLUTION
—————
Perform the
below action plan and re-run the Gather Stats and verify the results
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
);
Recent Posts