AlderPump logo

Version 2.5

Full support for 32 and 64-bit clients.

Covers all features of Oracle 10.1 to 11.2

Usability improvements and bugfixes

Export tables with their triggers, indexes, FK constraints, etc.

Download dump file Download job definition

In Table mode Data Pump operates on tables located within one schema (in Oracle 11g/R2 one schema restriction has been relaxed). By default, table and all their subordinate objects are processed.

This tutorial shows how to export two tables EMP and DEPT belonging to user SCOTT along with their accompanying objects.

Prerequisites:
  1. SCOTT schema
  2. DATA_PUMP_DIR Oracle directory with WRITE access
Demonstrated actions:
  1. Job Creation Wizard: Setting operation and mode, specifying dump file, selecting objects with Objects Picker dialog, submitting jobs.
  2. Main window: Job creation, job launching, progress log, job completion.

Tips:

Step 1/4: Selecting Operation and Mode

Since we are interested only in tables which located within the same schema, TABLE mode EXPORT would be our natural choice.

Selecting operation EXPORT and mode TABLES on Operations And Mode tab

Step 2/4: Specifying output file

Here we type name of the output dump file, select DIRECTORY object, and add the file to dump set. Note how AlderPump displayed file system path on database server machine to which Directory object is pointing. Should file already exist, export would fail unless we explicitly request to replace old file by checking Reuse flag. By default dump files are never overwritten, while log and sql files are always overwritten. We could also specify maximum file size, this is useful when specifying multiple files or using substitution variable %U.

Specifying scott_tables.dmp as output file

Step 3/4: Selecting objects to be exported

Specifying which objects should be exported occurs on Objects Filter page. Data Pump recognizes rich set of filter types, and AlderPump supports them all. In this tutorial, however, we will take a shortcut and use Objects Picker dialog which displays database schemas and objects and allows select them interactively. We marked tables of interest and dragged them onto 'Add objects' hotspot. AlderPump then added tables to the list. It has also figured their schema and added it to selection list. After all tables were selected, we close Objects Picker and AlderPump merges selected objects into list of filters. Expert users can add filters manually without invoking Objects Picker.

Adding metadata filter SCHEMA_LIST=SCOTT and TABLE_NAME=EMP, DEPT

Step 4/4: Running export

On creation, jobs enter DEFINING stage. At this stage they are ready to run, but still can be modified or cancelled. We start created job and monitor its progress in the Log window. If you ever ran expdp, you've probably noticed that AlderPump displays exactly same messages. That is because they both communicate with Oracle Data Pump engine which runs commands and reports statuses. In AlderPump, running job can be paused or cancelled with click of a button, but we don't do that here and just wait for the job to complete.

Creating and launching job. The job successfully completes.

Occasionally you may get error message when defining job:

add_file 'DATA_PUMP_DIR':'scott_tables.dmp', type 1, size ''
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3693
ORA-06512: at line 2

It means the output file already exist. Either delete the file on database server, or check "Reuse" flag on File panel for Data Pump to override it.