Export Import Schema Statistics

Gather Particular Schema Statistics

exec dbms_stats.gather_schema_stats(ownname => 'FINSTAGE',cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1' );

Transfering statistics between database.

It can be very handy to use production statistics on your development database, so that you can forecast the optimizer behavior.

You can do this the following way:

Create the statistics table.

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stat_tab => 'STATS_TABLE' , tblspace => 'STATS_TABLESPACE');
Example:
exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'STATS_TABLE');

Export statistics to statistics table

EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');

Import statistics into the data dictionary.

exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');


Drop the statistics table.
exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');

====Recommendations for Gathering Optimizer Statistics on 9i [ID 388474.1]====
Gathering statistics an individual table

exec dbms_stats.gather_table_stats( -
ownname => NULL, -
tabname => ' Table_name ', -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

N.B. replace ' Table_name ' with the name of the table to gather statistics for.

Gathering statistics for all objects in a schema

exec dbms_stats.gather_schema_stats( -
ownname => NULL, -
cascade => 'TRUE', -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );

Gathering statistics for all objects in the database:

exec dbms_stats.gather_database_stats( -
cascade => 'TRUE', -
method_opt => 'FOR ALL COLUMNS SIZE AUTO' );