ORA-20001: Invalid Column Name or Duplicate Columns/Column groups/expressions In method_opt
Symptoms:
Gather Schema Statistics" program reported following errors in request log files:
ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt
Statistics Errors:
stats on table FND_CP_GSM_OPP_AQTBL is locked
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP******
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 #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***
groups/expressions in method_opt***
Cause:
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> show user
USER is "APPS"
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
SQL>
Since there are two rows in histograms table, FND_STATS creates following command to gather statistics on table 'JE_BE_LINE_TYPE_MAP' :
Solution:
Find out all duplicates and/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.
SQL> select table_name, column_name, count(*) from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;
TABLE_NAME COLUMN_NAME COUNT(*)
------------------------------ ------------------------------ ----------
JE_BE_LOGS DECLARATION_TYPE_CODE 2
JE_FR_DAS_010 TYPE_ENREG 2
JE_FR_DAS_010_NEW TYPE_ENREG 2
JE_BE_LINE_TYPE_MAP SOURCE 2
JE_BE_VAT_REP_RULES SOURCE 2
JE_BE_VAT_REP_RULES LINE_TYPE 2
JE_BE_VAT_REP_RULES VAT_REPORT_BOX 2
JG_ZZ_SYS_FORMATS_ALL_B JGZZ_EFT_TYPE 2
-- Use above results on the following SQL to delete duplicates
SQL> delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and column_name = '&COLUMN_NAME'
and rownum=1;
Enter value for table_name: JE_BE_LOGS
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_BE_LOGS'
Enter value for column_name: DECLARATION_TYPE_CODE
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'DECLARATION_TYPE_CODE'
1 row deleted.
SQL> /
Enter value for table_name: JE_FR_DAS_010
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_FR_DAS_010'
Enter value for column_name: TYPE_ENREG
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'TYPE_ENREG'
1 row deleted.
SQL> /
Enter value for table_name: JE_FR_DAS_010_NEW
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_FR_DAS_010_NEW'
Enter value for column_name: TYPE_ENREG
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'TYPE_ENREG'
1 row deleted.
SQL> /
Repeat same till you delete all the duplicate rows and then commit.
SQL> select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;
no rows selected
SQL>
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
SQL>
SQL> commit;
Commit complete.
SQL>
No comments:
Post a Comment