Andrew Fraser DBA

dbms_stats export import optimizer statistics

Examples of syntax to export/import dbms_stats:

exec dbms_stats.create_stat_table ( ownname => user , stattab => 'temp_stats' ) ;
exec dbms_stats.export_table_stats ( ownname => user , stattab => 'temp_stats' , tabname => 'mytable' , statid => 'Taken_7Apr2010' ) ;
exec dbms_stats.import_table_stats ( ownname => user , stattab => 'temp_stats' , tabname => 'mytable' , statid => 'Taken_7Apr2010' )

Similarly for schema:

exec DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname => 'myowner' , stattab => 'temp_stats', statown => user )

A bug in 10g prevents spaces being passed into the “statid” variable, hence the underscores in the above example.

By default, index and column stats are included, specify cascade=>FALSE if you don’t want that.

Update 2015 – Note that (at least in 11gR2) import stats requires statid to be specified and match the statid used in the export, assuming a statid was specified in the export. The import procedure will do nothing otherwise, and without reporting an error to let you know something hasn’t worked. You can see the statid with:

SELECT statid , COUNT(*) FROM temp_stats GROUP BY statid ORDER BY 1 ;

Schema is recorded in the temp_stats table as column c5, so to change schema before importing stats:

UPDATE temp_stats SET c5 = 'new_schema' WHERE c5 = 'old_schema' ;

The parameters ownname, tabname, etc. are case insensitive.