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

Intro

Oracle Data Pump is a complex tool. It has many features controlled by over 80 parameters which interfere in sometimes unobvious ways. AlderPump makes their use a lot easier by disabling non-applicable pages and warning about incompatible combinations - but still, sometimes it is hard to figure the right one. These mini-tutorials were created by AlderProgs to guide you through some common tasks and perhaps demonstrate a trick or two.

The screenshots show how to perform tasks in AlderPump, but because of its unique Command Line generation feature, you can always see corresponding expdp or impdp command. Each tutorial includes AlderPump saved job which you can load into AlderPump's Job Creation Wizard and run by yourself.

Configuring Oracle Data Pump and AlderPump

Setup

The tutorials use Oracle sample schemas: HR, OE, PM, IX, SH, and all times favorite tiger SCOTT. Please refer to Oracle Sample Schemas guide for their description, diagrams, and setup instructions.

For file locations, we are using Oracle directory DATA_PUMP_DIR which is typically created at database installation. Oracle Utilities guide's section Default Locations for Dump, Log, and SQL Files provides more details on the directory and other available options.

Although we run all examples as user SYSTEM, its schema never get modified by our actions. We always operate on other schemas, and SYSTEM is used merely for connecting to the database.

To demonstrate network imports, we've created public database link LOOPBACK and granted access to it to PUBLIC. The link connects with current user's credentials and points to our database:

    CREATE PUBLIC DATABASE LINK loopback USING 'tns_alias';

Finally, for file browser tutorials, we configured server-side directory browsing using PL/SQL method as explained in AlderPump manual.

Basic tasks

In this section we concentrate on simple everyday tasks such as moving table data around. This includes exporting data from tables and re-importing it into tables located on the same or different database. Later sections care more about moving around object definitions, or metadata; here we stick to operating on table contents of tables rather than table's structure.

Table mode operations

Table operations are the simplest. As their name implies, they operate on objects located in one single schema (multiple schemas since v11.2), but limited to tables and their associated objects: triggers, foreign key constraints, indexes, comments, and the like. Note that objects like views, synonyms, types, packages, sequences, etc are not associated with tables (although depend on them); in table mode such objects are not processed.

bulb How to create or fix sample schema should it become corrupted
Tutorials below work on SCOTT schema. If things go wild and the schema becomes corrupted, you can always revert back by re-creating it. On database server machine, login to SqlPlus as privileged user and run utlsampl.sql:
On Unix: @$ORACLE_HOME/rdbms/admin/utlsampl.sql
On Windows:@?\rdbms\admin\utlsampl.sql
Note that account SCOTT is locked by default; since we drop and re-create it, the account becomes unlocked. Also be aware that the script drops schemas ADAMS, JONES, CLARK and BLAKE.

Schema mode operations

Unlike Table mode which only works on tables and their "satellites", in schema mode Data Pump processes objects of any type. Beside objects with data (tables and materialized views), it starts to recognize ones which are essentially metadata - constraints, grants, stored procedures... any type Oracle database may contain. Another important point is that now definition of the user is exported - including default/temporary tablespaces and grants.

For tutorials below, schema SCOTT with its 4 tables and 2 indexes is too simple; we are going to use OE which has wider variety of object types: a function, a sequence, synonyms, triggers, views, types and type bodies - and of course a number of tables.