Export and Import schema statistics

posted Jun 4, 2012, 7:14 PM by Sachchida Ojha

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.

SCHEMA NAME – SACHI


Create the table to store the statistics-
EXEC DBMS_STATS.CREATE_STAT_TABLE ( OWNNAME => 'SACHI' , STATTAB => 'TEMP_STAT' ) ;

Export schema stats – will be stored in the temp_stat
exec DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'SACHI',STATTAB=>'TEMP_STAT');

If required import these statistics back to TEST schema.
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME => 'SACHI' , STATTAB => 'TEMP_STAT');

Finally drop the table created to backup the schema stats
EXEC DBMS_STATS.DROP_STAT_TABLE( OWNNAME => 'SACHI' , STATTAB => 'TEMP_STAT');

Comments