Setup Menus in Admin Panel

DBATalent

Error While Running Gather Schema Statistics

Knowledge Base > Oracle Applications > Error While Running Gather Schema Statistics

in Oracle Applications

The below article will give you the steps to solve the issue while running gather schema statistics.

 

Issue1:  Error while running Gather Schema Statistics.

 

Erros_msg_Gather_statics_1

 

Error Message:

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

 

Cause:

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;

 

Solution:

Find out all duplicates or obsolete rows in FND_HISTOGRAM_COLS and delete one of them logged in as the applsys user. Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

  • Execute the below query to list tables which are having duplicate rows.

SQL>select table_name, column_name, count(*) from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

 

  • Take a backup of FND_HISTOGRAM_COLS table.

SQL> create table FND_HISTOGRAM_COLS_FEB2016 as select * from FND_HISTOGRAM_COLS;

 

  • Identify the duplicate rows using below query. It lists the tables with the column name and duplicate number of rows.

SQL>select column_name, nvl(hsize,254) hsize from FND_HISTOGRAM_COLS where table_name = ‘&Table_name’ order by column_name;

 

  • Use the following SQL to delete duplicate rows.

SQL>delete from FND_HISTOGRAM_COLS where table_name = ‘&TABLE_NAME’ and column_name = ‘&COLUMN_NAME’ and rownum=1;

SQL> commit;

Enter the values for &Table_name and &Column_name to delete duplicate rows.

  • Use following SQL to delete obsoleted rows.

 

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

SQL> commit;

 

  • Verify that now the tables are not listed in FND_HISTOGRAM_COLS

SQL> select table_name, column_name, count(*) from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

 

Now run again Gather schema stats with ALL schemas with 10% estimate.

0