In this section:
In version 10g, Oracle Corporation introduced an enhancement to export and import utilities: Oracle Data Pump. Just like old exp and imp the new utilities called expdp and impdp are launched from command line. But their behavior differs:
Old utilities execute the operation on the machine they were started, i.e. on the client. Export queries data from the database server, transfers data over network to the client, and stores them to files. Similarly, import reads data from files on the local computer and sends them over the network to database.
Data Pump works differently. The operation is still initiated from client computer, but expdp/impdp merely send commands to Oracle server for execution. They are calling PL/SQL package DBMS_DATAPUMP to initiate and monitor the operation, but the processing occurs on the server.
AlderProgs AlderPump works similarly: it is calling DBMS_DATAPUMP to initiate the desired operation and then waits for the feedback. The package is described in Oracle documentation and therefore open for developers.
Hover mouse over image to animate
May take time on slow connections
Server-side execution speeds up exports and imports as network latencies are eliminated, but it also imposes several limitations. Obviously, now it is impossible to create export dumps on the client: they are created on the database server machine. Similarly, import expects the files to be accessible by Oracle server processes. For large dump files space should be allocated; the space can reside on a network share though. Another potential pitfall is the impact introduced to the server machine. Not only the engine is involved now; the file system is also busy reading and writing files. While Unix systems usually tolerate such type of load, Windows boxes may literally freeze while performing intensive file system IO.
Still, today Data Pump is the fastest way to get data in and out of Oracle databases leaving aside low-level methods such as transportable tablespaces and RAID mirror splitting. In fact, Data Pump does support transportable tablespaces: it is one of the modes exposed by Oracle command line utilities and AlderPump.
Speed is an important factor; however Data Pump offers other advantages. Jobs can now be paused and restarted, their parameters (such as degree of parallelism) changed dynamically, because of server-side executions users can attach and detach from running jobs without affecting them. Data Pump also provides fine grained control on objects being exported and imported. For example, one can restrict export to process only certain types of objects. Alternatively, specified types of objects can be ignored if listed in exclusion list.
Beside speed and manageability, Data Pump offers some greater flexibility on transformation of data and metadata, making it a rudimentary ETL tool:
- Objects (also called Metadata) filter allows to select or deselect objects based on their name, type, schema, tablespace, etc. Values can be specified as list or expression. This is most frequently used filter as it tells Data Pump what to process, but it is also the most obfuscated. Applicability of filters and object types depend on type of the operation (export/import), mode (table/schema/full/etc) and database version. Fortunately, AlderPump simplifies filter selection by providing visual hints and populating drop down lists with allowed values only.
- Data filters work on individual tables. Include rows filter allows to list tables whose rows should be processed, Exclude rows works similarly. For fine grained filtering use Subquery filter which selects only rows satisfying custom WHERE condition. For unloading subset of random data to test system, use Sample filter - specified percentage of rows is picked randomly. Or, employ Partitions filter to include/exclude selected partitions.
- Metadata remapping available at import time, transforms object properties basing on its name. This filter is extremely handy when importing objects to another schema or tablespace. The filter may operate basing on object's type, making it ideal for tasks such as separating indexes and data into different tablespaces (feature available in AlderPump only1). Datafile names can also be changed to reflect their desired location.
- Data remapping has became available in Oracle 11g. It allows transformation of column values using custom function. The function takes parameter of the same type as the column, and returns modified value. Its potential usage varies greatly from depersonalizing private information such as social security or credit card numbers to data massaging.
- Space transformation filter can be used to shrink or grow storage allocated to objects. Say, when exporting 10% of the data with Sample data filter, it makes sense to "zoom" segment size appropriately. This filter can also be used to suppress physical and storage attributes when generating sql file with SQL_IMPORT operation.
- Encryption works on all or selected columns. The encryption can be based on password, wallet, or both - and uses AES cryptographic algorithm with key size from 128 to 256 bits.
- Various parameters control execution of Data Pump jobs. You can perform a dry run estimating space needed with Estimate parameter. Or, ensure data exported data consistency by performing snapshot export based on time of day or SCN. There are options to control what to do when table being imported already exists, whenever to skip constraint errors, and many others.
AlderPump comes in two flavors: Lite and Professional. Lite edition is free, but its functionality is limited. The two major limitations of Lite Edition are:
- Only current user's jobs are monitored
- Detailed job creation is not available in Job Creation Wizard.
For job creation in Lite mode, four wizards are currently available to create export and import jobs in schema and table modes.
Professional mode, becoming available after installing license key, unlocks full power of AlderPump along with features provided by API but missing in command line utilities. Here is comparison chart:
| AlderPump Professional | expdp/impdp |
|---|---|
| Remap tablespace depending on object type: place tables and indexes into separate tablespaces on import1. | Remap all objects in a tablespace to another tablespace. |
| Add job comments: either at creation time or while job is running. The comments may be optionally made visible to other AlderPump users. | Not supported |
| Disable processing of rows for all or individual tables | Row processing can only be disabled for all tables |
| Can specify any number of include and exclude filters. | Can only specify either include or exclude objects filter. |
| Specify any number of wildcard expressions. | Can specify only one wildcard expression when filtering objects |
| Limit size of each dump file individually. | One size limit for all dump files. |
| Browse, upload, download, rename, delete, and view files on database server side via GUI. | No support |