Exporting/Importing Schema Statistics

1. Create the statistics table.

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stattab => 'STATS_TABLE' , tblspace => 'STATS_TABLESPACE');

Example:

exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stattab => 'STATS_TABLE');

2. Export statistics to statistics table

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

3. Import statistics into the data dictionary.

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

4. Drop the statistics table.

exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');