Wednesday, August 21, 2013

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

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

delete from FND_HISTOGRAM_COLS
where table_name = 'JE_FR_DAS_010'
and  column_name = 'TYPE_ENREG'
and rownum=1;


delete from FND_HISTOGRAM_COLS
where table_name = 'JE_FR_DAS_010_NEW'
and  column_name = 'TYPE_ENREG'
and rownum=1;


delete from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
and  column_name = 'SOURCE'
and rownum=1;

delete from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LOGS'
and  column_name = 'DECLARATION_TYPE_CODE'
and rownum=1;


delete from FND_HISTOGRAM_COLS
where table_name = 'JG_ZZ_SYS_FORMATS_ALL_B'
and  column_name = 'JGZZ_EFT_TYPE'
and rownum=1;


delete from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_VAT_REP_RULES'
and  column_name = 'LINE_TYPE'
and rownum=1;



delete from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_VAT_REP_RULES'
and  column_name = 'SOURCE'
and rownum=1;



delete from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_VAT_REP_RULES'
and  column_name = 'SOURCE'
and rownum=1;


delete from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_VAT_REP_RULES'
and  column_name = 'VAT_REPORT_BOX'
and rownum=1;

1 comment:


  1. create table FND_HISTOGRAM_COLS_BKP as select * from FND_HISTOGRAM_COLS ;

    select * from FND_HISTOGRAM_COLS_BKP ;


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

    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_FR_DAS_010'
    and column_name = 'TYPE_ENREG'
    and rownum=1;


    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_FR_DAS_010_NEW'
    and column_name = 'TYPE_ENREG'
    and rownum=1;


    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_BE_LINE_TYPE_MAP'
    and column_name = 'SOURCE'
    and rownum=1;

    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_BE_LOGS'
    and column_name = 'DECLARATION_TYPE_CODE'
    and rownum=1;


    delete from FND_HISTOGRAM_COLS
    where table_name = 'JG_ZZ_SYS_FORMATS_ALL_B'
    and column_name = 'JGZZ_EFT_TYPE'
    and rownum=1;


    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_BE_VAT_REP_RULES'
    and column_name = 'LINE_TYPE'
    and rownum=1;



    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_BE_VAT_REP_RULES'
    and column_name = 'SOURCE'
    and rownum=1;



    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_BE_VAT_REP_RULES'
    and column_name = 'SOURCE'
    and rownum=1;


    delete from FND_HISTOGRAM_COLS
    where table_name = 'JE_BE_VAT_REP_RULES'
    and column_name = 'VAT_REPORT_BOX'
    and rownum=1;

    ReplyDelete