The statistics export feature is mainly used to export the statistics of a table and save them in a specified user table. The import feature, on the other hand, is used to import the statistics from a specified user table into the current database, eliminating the need to recollect the statistics. This functionality is useful, for example, during database data migration, where the table’s statistics can be exported to a specified user table for migration. After the data migration is successful, the previously saved statistics from the user table can be directly imported into the new database.
The DBMS_STATS package provides the following procedures for importing and exporting 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 TEST 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');