Prev: Problem with network access Oracle 10g
From: dba cjb on 11 Aug 2010 05:26
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(a)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
From: Mark D Powell on 11 Aug 2010 08:48
On Aug 11, 5:26 am, dba cjb <chris.br...(a)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(a)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 --
 | 
Pages: 1
Prev: Problem with network access Oracle 10g