Nice little best practice for statistics and Data Pump when doing either Transportable Tablespaces or Full Transportable Export-Import (credits to Roy and Dean Gagne).
Transport Statistics via a Staging Table
First of all we always recommend to exclude statistics when doing a Data Pump export as the import of such stats takes way longer than transporting them via a stats table. If you are unfamiliar with transporting stats between databases please see the Oracle Performance Tuning Guide with a nice tutorial:
The basic steps to transport statistics from one database to another fast and efficient consist of:
- Create a staging table in your source database with
- Export your local stats into this staging table by using DBMS_STATS.EXPORT_SCHEMA_STATS
- Export the staging table and import it into your destination database with Data Pump
- Import the statistics held in the staging table by using DBMS_STATS.IMPORT_SCHEMA_STATS
For the regular Data Pump exports we always recommend to set:
to avoid performance penalties during impdp.
But this does not affect Transportable Tablespaces and Full Transportable Export/Import.
How to exclude Statistics for TTS and FTEX?
*** ATTENTION: THIS IS NOT NECESSARY ANYMORE AT LEAST SINCE ORACLE 19c ***
For reasons I don’t know the metadata heterogeneous object for “transportable” is different than all of the others Therefore in order to exclude statistics for Transportable Tablespaces and Full Transportable Export/Import you must set:
Keep this in mind during your next migration 🙂
Other Useful Articles About Data Pump
Some Data Pump issues: + DBMS_DATAPUMP Import via NETWORK_LINK fails + STATUS parameter giving bad performance
Parallel Index Creation with Data Pump
How to get the Master Table from Data Pump?
Exclude Scheduler Jobs from Export?
Full Transportable Export/Import White Paper