Here’s my contribution to the OTN Appreciation Day.
Data Pump (expdp, impdp) was added in Oracle 10g as a replacement for the rather tired exp/imp utilities, which although useful, were severely lacking in functionality. So why do I like Data Pump? Here are a few standouts for me, but I’m sure other people will prefer others. 🙂
- Transportable Tablespaces, and from 12c onward Transportable Database, make moving large amounts of data, or even whole databases easy. This can include platform/endian changes and version upgrade too. This was possible with exp/imp too, but it doesn’t stop it being a useful feature of Data Pump. 🙂
- The INCLUDE, EXCLUDE, CONTENT and QUERY parameters allow a lot of flexibility about what you include or exclude from your exports and imports.
- The FLASHBACK_TIME parameter allows you to do an export of the data based on a point in time, undo permitting, which allows you to make truly consistent exports.
- The REMAP parameters allow you to rename tablespaces, datafiles, schemas, tables and even alter the data during operations.
- The DBMS_DATAPUMP package provides a PL/SQL API, allowing you to perform Data Pump operations without having to shell out to the OS. That makes automation a lot simpler.
- The NETWORK_LINK parameter can be used to perform export and import operations over a database link. This allows you to create a dump file on a remote database, or even import without an intermediate dump file.
I’m sure beginners think Data Pump is just for doing exports and imports of small databases, but it’s got loads of features. If you’ve not kept up with the new releases, you might be surprised just how much it can do!
If you’re interested in reading something more technical about Data Pump, here are some articles from my website.
- Oracle Data Pump (expdp and impdp) in Oracle Database 10g
- Data Pump Enhancements in Oracle Database 11g Release 1 (expdp and impdp)
- Data Pump Enhancements in Oracle Database 12c Release 1 (expdp and impdp)
- SQL Developer 3.1 Data Pump Wizards (expdp, impdp)
- Transportable Tablespaces
- Upgrading to Oracle Database 12c – Transportable Database