• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Ebuisness Suite » ORA-20001 in R12 Gather schema statistics on 11g(FND_HISTOGRAM_COLS)

ORA-20001 in R12 Gather schema statistics on 11g(FND_HISTOGRAM_COLS)

April 12, 2012 by techgoeasy Leave a Comment

“Gather Schema Statistics” program reported the following errors in request log files :

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

Error #4: ERROR: While GATHER_TABLE_STATS: object_name=FII.FII_FIN_ITEM_HIERARCHIES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

This occurred if there are duplicate or invalid columns in FND_HISTOGRAMS_COL

(1 ) There are duplicate rows on the FND_HISTOGRAM_COLS table for the JE_BE_LINE_TYPE_MAP table. Because of this problem, FND_STATS tries to gather histogram information using the wrong command and it fails with ORA-20001 errors.

The following SQL should return one row, not two:

SQL>select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;

COLUMN_NAME HSIZE
------------------------------ ----------
SOURCE 254
SOURCE 254

Since there are two rows in the histograms table, FND_STATS creates the following command to gather statistics on table ‘JE_BE_LINE_TYPE_MAP’ :

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

The above command will work on 9i and 10G databases but it will fail with ora-20001 errors on 11G.

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

You can use the following SQL to identify. SQL will prompt for the table name, use table name from the errors. In the above examples, you can use FII_FIN_ITEM_HIERARCHIES.

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;

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them. Remember to take a 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 the 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 the 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
);

Also Reads
ORA-00900 : Invalid Sql statement
ORA-03113
ora-29283: invalid file operation
ORA-29285: file write error
ORA-00054

Filed Under: Oracle, Oracle Ebuisness Suite Tagged With: FND_HISTOGRAM_COLS, gather schema, ora-20001 in Gather schema stats

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us