Re: Stats gathering (8i) to non-data dictionary tables

Response from Oracle below. Looks like it isn't possible without hitting the dd...

hi,

In theory , there is a roundabout way of doing it:-

1. Save the current stats:-

exec dbms_stats.create_stat_table('user','CURRENT_STATSTAB');

exec dbms_stats.gather_schema_stats(STATID => 'current', ownname=>'user', stattab=>'CURRENT_STATSTAB', statown=>'owner of current_statstab',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL', granularity=>'ALL' );

This will collect the stats and place the 'old' stats in the current_statstab table.

Create a table to hold the new stats in :-

exec dbms_stats.create_stat_table('user','NEW_STATSTAB');

And regather again so the 'new' stats are saved:-

exec dbms_stats.gather_schema_stats(STATID => 'new', ownname=>'user', stattab=>'NEW_STATSTAB', statown=>'owner of current_statstab',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL', granularity=>'ALL' );

Now, restore the old stats:-

exec
dbms_stats.import_table_stats('user',NULL,NULL,'CURRENT_STATSTAB','current',TRUE);

..this is the only possible way of doing it and is not ideal by any stretch. You will need to test the above on a test instance. Received on Wed Aug 03 2005 - 08:06:06 CDT