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
#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***
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:
SQL>
select column_name, nvl(hsize,254) hsize
from
FND_HISTOGRAM_COLS
where
table_name = 'JE_BE_LINE_TYPE_MAP'
order
by column_name; 2 3 4
COLUMN_NAME
HSIZE
------------------------------
----------
SOURCE
254
SOURCE
254
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
SQL> delete from FND_HISTOGRAM_COLS
where table_name = '&TABLE_NAME'
and column_name = '&COLUMN_NAME'
and rownum=1; 2 3 4
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> /
Enter value for table_name: JE_BE_LINE_TYPE_MAP
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_BE_LINE_TYPE_MAP'
Enter value for column_name: SOURCE
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'SOURCE'
1 row deleted.
SQL> /
Enter value for table_name: JE_BE_VAT_REP_RULES
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_BE_VAT_REP_RULES'
Enter value for column_name: SOURCE
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'SOURCE'
1 row deleted.
SQL> /
Enter value for table_name: JE_BE_VAT_REP_RULES
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_BE_VAT_REP_RULES'
Enter value for column_name: LINE_TYPE
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'LINE_TYPE'
1 row deleted.
SQL> /
Enter value for table_name: JE_BE_VAT_REP_RULES
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JE_BE_VAT_REP_RULES'
Enter value for column_name: VAT_REPORT_BOX
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'VAT_REPORT_BOX'
1 row deleted.
SQL> /
Enter value for table_name: JG_ZZ_SYS_FORMATS_ALL_B
old 2: where table_name = '&TABLE_NAME'
new 2: where table_name = 'JG_ZZ_SYS_FORMATS_ALL_B'
Enter value for column_name: JGZZ_EFT_TYPE
old 3: and column_name = '&COLUMN_NAME'
new 3: and column_name = 'JGZZ_EFT_TYPE'
1 row deleted.
SQL> select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1; 2 3 4
no rows selected
SQL> commit;
Commit complete.
I fixed the issue and rerun the above query and it returned
0 rows which means that the issue was killed.
SQL> select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1; 2 3 4
no rows selected
Regards
Mohamed ELAzab
