High Speed Oracle Data Extract and Reload Oracle Database Tips by Donald Burleson

Question:  I need to offload a huge Oracle database onto flat files and export is too slow.  I also find the import utility (imp and impdp) too slow.  What are other options for fast data unloading and reloading from Oracle?

Answer:  There are many tool, many vendors, and many techniques.  I recommend the book "Oracle Utilities" for tips on improving Oracle unload/reload performance and my book "Oracle Tuning: The Definitive Reference" for specific tuning tips for high I/O operations.  Also see:

• Data Pump - Here are my tips for speeding up Oracle expdp (Data Pump).  You can also tune the import utility (impdb) for faster performance.
   
• Solid-state disk - SSD is hundreds of times faster than disk, and it's perfect for super-fast unload/reload migrations.
   
• SQL Loader - You can make SQL*Loader run very fast with tuning tips.
   
• Database to database - Here is a clever technique using Linux with direct export/import, with no intermediate flat files.
   
• CTAS - You can use parallelized "create table as select" over a high-speed database link to quickly move tables between Oracle databases.
   
• Fact & CoSort - Fast Extract (FACT) for Oracle unloads large tables in parallel to flat files.  FACT also writes the file layout metadata that CoSort can use for reload (reorg, ETL) pre-sorts, plus join and aggregate transforms, report generation, field-level security, etc.  For more information about these tools click here.
   
• Vendor tools - Vendors such as BMC and Wisdomforce offer tools for super-fast table unloading from Oracle.  Also, unload/reloads happen super-fast on SSD hardware.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.