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 schema

Download dump file Download job definition

In Schema mode Data Pump exports all objects in the schema including user definition - unless something was explicitly filtered out.

This tutorial shows how to export schema OE, which is part of Oracle sample schemas.

Prerequisites:
  1. OE schema
  2. Oracle directory DATA_PUMP_DIR with WRITE access
Demonstrated actions:
  1. Job Creation Wizard: setting operation and mode, specifying dump file, specifying schema to export, submitting jobs.
  2. Main window: Job creation, job launching, progress log, job completion.

Tips:

Schema mode is the default, AlderPump pre-selects it for new jobs. All we need to do is select EXPORT operation.

Selecting operation EXPORT and mode SCHEMA on Operations And Mode tab

Step 2/4: Specifying output file

We type in name of the output dump file. DIRECTORY object already set to DATA_PUMP_DIR which is our destination, no need to change it. File type was also pre-set to "dump file", no need to change that either. We click on Add button to add dump file to the set. Like in Table-mode export, we could limit maximum file size or use substitution variable %U.

Download result file (zip archive, ~770K)

Specifying oe.dmp in DATA_PUMP_DIR as output dump file

Step 3/4: Selecting schemas to be exported

We could create and launch job after specifying dump file; however that would export schema of the connected user, i.e. SYSTEM. To export another schema, SCHEMA_LIST or SCHEMA_EXPR filter is needed. There is no restriction on number of schemas exported, but in this demo we only specify one value: OE. We could launch Objects Picker and drag schema OE to "add objects" hotspot, but it is quicker to just type it in.

On Object Filter page, adding filter OE of type SCHEMA_LIST

Step 4/4: Running export

After job is created and entered DEFINING stage, we start it. Compared to TABLE export, number of processed object types is significantly higher: there are grants, roles, sequences, views, functions - we didn't see any of those when ran TABLE mode export. Note the first type exported - it is definition of OE user.

Creating and launching job. The job successfully completes.

Like with table export, if the step fails with the error:

add_file 'DATA_PUMP_DIR':'oe.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 indicates that the output file already exists. Either delete the file on database server, or check "Reuse" flag on File panel of Job Creation wizard for Data Pump to override it.