Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade

http://www.conacent.com

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.

Following SQL should have returned 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


Solution :-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;




JE_FR_DAS_010
TYPE_ENREG

JE_FR_DAS_010_NEW
TYPE_ENREG

JE_BE_LINE_TYPE_MAP
SOURCE

JE_BE_LOGS
DECLARATION_TYPE_CODE

JG_ZZ_SYS_FORMATS_ALL_B
JGZZ_EFT_TYPE

JE_BE_VAT_REP_RULES
LINE_TYPE

JE_BE_VAT_REP_RULES
SOURCE

JE_BE_VAT_REP_RULES
VAT_REPORT_BOX



select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');

SQL> select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null
and owner not in ('SYS');


OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
SYSTEM                         TBLMIG_MSG_QTAB                ALL
SYSTEM                         DEF$_AQERROR                   ALL
SYSTEM                         DEF$_AQCALL                    ALL
ODM                            DMS_QUEUE_TABLE                ALL
APPLSYS                        FND_CP_GSM_IPC_AQTBL           ALL
APPLSYS                        WF_NOTIFICATION_OUT            ALL
APPLSYS                        AQ$_WF_CONTROL_P               ALL


7 rows selected.

SQL>

From the above list 3 tables owned by APPS/APPLSYS shows as locked. Use the below API to release these locks and re-attempt the statitics task. Should be Ok Now.

exec dbms_stats.unlock_schema_stats('schema_owner');

In this case it will be APPS and APPLSYS

exec dbms_stats.unlock_schema_stats('APPLSYS');



SQL> exec dbms_stats.unlock_schema_stats('APPLSYS'); 


1. Run query to find the list of indexes which are in unusable status :

select owner, index_name, index_type,
table_owner, table_name, tablespace_name,
status, last_analyzed
from dba_indexes
where status='UNUSABLE';