Re: Gather Schema Statistics on R12.2

Posted by ErmanArslansOracleBlog on Nov 14, 2014; 12:55pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Gather-Schema-Statistics-on-R12-2-tp96p97.html

Since your database is 11G , it seems you are encountring this error because of the duplicate records present in your fnd_histogram table..

Backup FND_HISTOGRAM_COLS table;

Then do the following;

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

commit;