Export and Import schema statistics

You would do this ,in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.

SCHEMA NAME – TEST ( any of your schema name )

Create a table to store the statistics in your production database.
EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’);

Export schema statistics in production database– will be stored in the ‘STATS_TABLE’
EXEC DBMS_STATS.export_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);

If required import these statistics back to TEST schema in staging or testing database.
EXEC DBMS_STATS.import_schema_stats(‘TEST’,'STATS_TABLE’,NULL,’TEST’);

Finally drop the table created to backup the schema stats in production database.
EXEC DBMS_STATS.drop_stat_table(‘TEST’,'STATS_TABLE’);