• 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 » 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 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 column in FND_HISTOGRAMS_COL

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using 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 histograms table, FND_STATS creates 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');

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 still listed in FND_HISTOGRAMS_COL table.

You can use following SQL to identify. SQL will prompt for table name, use table name from the errors. In 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 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
);

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

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • Query to check table size in Oracle database
  • how to check all constraints on a table in oracle
  • Oracle Indexes and types of indexes in oracle with example
  • ORA-01017 Oracle Error Resolution Tips



Subscribe to our mailing list

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

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

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