Hai, this article is for clearing
the error ORA-20001 when you run Gather
Schema Statistics for ALL users
EBS Version: R12.1.3
Database Version: 11.1.0.7.0
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***
Investigating the
issue I find that the historgram contains duplicate records which it should has
only one record. This
is a known issue after upgrade and should be handled as per below:
The query result below
show that we are impacted by this issue:
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>
SQL>
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
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>
ORA-20001 ISSUE RESOLVED
This article helps those who facing issue in gather schema stats
Request.
Thanks for Reading.
Regards,
Mohammed Areefuddin.
Suggested Topics :
Linux
|
DATABASE
|
RMAN
|
RAC
|
EBS
|
EBS DataGuard
| ||||
No comments:
Post a Comment