Friday, December 12, 2014

EBS 12.2 -- GATHER_SCHEMA_STATS , ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt

This may be seen in a Fresh EBS 12.2.* system, while gathering the statistics using "Gather Schema Statistics" concurrent program.The cause of this issue is the duplicate rows in FND_HISTOGRAM_COLS. This makes the FND_STATS building a wrong command for gathering the histogram information.. A wrong command like below;

dbms_Stats.gather_table_stats(OWNNAME => 'GL', TABNAME
=>'JE_BE_LINE_TYPE_MAP',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS COLUMN_NAME SIZE COLUMN_SIZE FOR
COLUMNS COLUMN_NAME SIZE COLUMN_SIZE
');

This issue is completely addressed in Oracle Doc: 11i - 12 Gather Schema Statistics fails with Ora-20001 errors after 11G database Upgrade (Doc ID 781813.1)
The issue is said to be present for 11g databases, as a command like the one above will work on 9i and 10G databases.

Okay..
I can say that it is applicable for EBS 12.2 too..
Because we encounter the same errors, and we use 11gR2 bundled with EBS.
For the solution, you may follow the document mentioned above or you can use the following dynamic sql to build the delete commands for you..

Use the dynamic sql below to prepare the delete commands you need.. Then execute them.. Dont forget to commit..

select  'delete from FND_HISTOGRAM_COLS where table_name='''||table_name||''' and column_name='''|| column_name||''' and rownum=1;' as "FIRST execute this",
'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
  )' as "then execute  this"
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1

Once you delete the duplicate and  obsoleted rows in FND_HISTOGRAM_COLS tables, retry Gathering Statistics using Gather Schema Statistics concurrent program..

No comments :

Post a Comment