Oracle DBA Tips and Techniques

By Arjun Raja

Export and Import schema statistics

Posted by Gavin Soorma on June 25, 2009

Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.


Create the table to store the statistics-

Export schema stats – 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.
EXEC DBMS_STATS.import_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’);

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: