You can export statistics on a table to a specified user table and import the statistics in a specified user table to a database without re-collecting the statistics. This feature is useful in many scenarios. For example, when you migrate data between databases, you can export statistics on tables to a specified user table, migrate this user table to the target database, and then import the statistics from the user table to the target database.
The DBMS_STATS package provides the following procedures for you to import and export statistics:
create_stat_table: creates a user table to store statistics.drop_stat_table: drops a user table that stores statistics.export_table_stats: exports table-level statistics.export_index_stats: exports index statistics.export_column_stats: exports column-level statistics.export_schema_stats: exports statistics of all tables in a schema.import_table_stats: imports table-level statistics.import_index_stats: imports index statistics.import_column_stats: imports column-level statistics.import_schema_stats: imports statistics for all tables in a schema.
# Create a statistics user table named TEST_STAT for the TEST user.
call dbms_stats.create_stat_table('TEST', 'TEST_STAT');
# Export statistics on the T1 table under the TEST user to the TEST_STAT table.
call dbms_stats.export_table_stats('TEST', 'T1', stattab=>'TEST_STAT', statown=>'TEST');
# Import statistics on the T1 table under the TEST1 user from the TEST_STAT table under the TEST user.
call dbms_stats.import_table_stats('TEST1', 'T1', stattab=>'TEST_STAT', statown=>'TEST');
# Delete the TEST_STAT table under the TEST user.
call dbms_stats.drop_stat_table('TEST', 'TEST_STAT');