Re: refresh stats problem

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 11 Aug 2010 05:48:34 -0700 (PDT)
Message-ID: <88f752c5-9bf0-4d8c-93b1-58d34857d7d6_at_i13g2000yqd.googlegroups.com>

On Aug 11, 5:26 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> Oracle 10.2.0.4 enterprise  on windows
>
> I wanted to rebuild stats on server b ( copy )  from a saved table on
> server b
>
> Note:- stats have been exported into HIMSDBA.STATTAB
>
> On server B
>
> 1) CREATE TABLE LIVE.CJBTAB AS SELECT * FROM HIMSDBA.STATTAB_at_SERVERA
> WHERE D1 < TO_DATE('15/07/2010','DD/MM/YYYY')
> AND STATID LIKE 'LIVE%';
>
> 2) BEGIN
> DBMS_STATS.delete_schema_stats('LIVE');
> END;
>
> 3) BEGIN
> DBMS_STATS.IMPORT_SCHEMA_STATS('LIVE','CJBTAB',NULL,'LIVE');
> END;
>
> I was hoping that the process would recreate histograms / table stats
> but it doesn't seem to have worked
>
> Could anyone advise on if what I am trying should work or if I've
> missed something?
>
> cheers
> Chris B

Since you pull the data into the target database via a database link why not just calculate the statistics on the freshly created and populated table in the target database?

From you post I cannot tell how you moved the statistics from the source db to the target and who owns the stats table in use. It may help if you explicitly provide all parameters. Also did you verify that histograms exist on the source for the object in question? That is, if you have statistics but not the statistics you expected it may be because the source does not have the statistics expected.

Why not just use the import_table_stats procedure instead of schema?

HTH -- Mark D Powell -- Received on Wed Aug 11 2010 - 07:48:34 CDT