本文共 3080 字，大约阅读时间需要 10 分钟。

GOAL

This article describes the steps required to transfer the table/index statistics from one database to another.

Discussion will cover how to accomplish this via table level, schema level, and database level.

SOLUTION

1. Create the STATS table

2. Export the statistics to the STATS table

Retrieving the statistics can be achieved using EXPORT_XXXX_STATS procedure:

• Exporting the statistics of all the objects in the database
  EXEC DBMS_STATS.EXPORT_DATABASE_STATS(stattab => 'STATS');
    
  Make sure you have logged in as SYS or SYSTEM to export database level statistics
• Exporting the statistics of all the objects in a schema
  EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'user', stattab => 'STATS');
    
  You need to have DBA privileges to export the statistics of other schemas.
• Exporting the statistics of tables
  EXEC DBMS_STATS.EXPORT_TABLE_STATS(<'username' or NULL>,'TAB1',NULL,'STATS');
    

3. Export the STATS table using export(exp) or datapump(expdp)

4. Transfer the dump file to the destination database

If you are using ftp to transfer the files make sure that you are transferring the file with binary option to avoid the dump file being corrupted.

5. Import the STATS table to the destination database

6. Import the statistics into the data dictionary

The statistics can be imported using IMPORT_XXXX_STATS procedure:

• Importing the statistics for all the objects in the database
    
  EXEC DBMS_STATS.IMPORT_DATABASE_STATS(stattab => 'STATS');
    
• Importing the statistics to all the objects in a schema
  EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => user, stattab => 'STATS');
    
• Importing the statistics to tables 
   
  EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname => user, tabname => 'TAB1', stattab => 'STATS');
    

7. Changing the schema name

Statistics cannot be directly exported from one schema and imported into a different schema.The schema names much match exactly.

If the target database schema is different from the source database schema, the schema name can be changed by updating column C5 of the STATS table:

8. Upgrading statistics table

When importing statistics from an earlier version into a later version of Oracle, the structure of the statistics table may have changed.

Try executing DBMS_STATS.UPGRADE_STAT_TABLE to resolve this

转载地址：https://blog.csdn.net/wll_1017/article/details/47375155 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！