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

Importing tables into different tablespace

Download job definition

Data Pump stores tablespace information along with objects. On import, when trying to create table or index, it creates objects in exactly same tablespace as their original. At imp times DBAs developed several methods to overcome that - like pre-creating objects or disabling quota on target tablespaces making imp fallback to user's default one. But these methods are either not flexible enough or include manual steps. Oracle Data Pump offers a better way.

If you followed Importing into another schema tutorial, you know that Data Pump can tweak some of object's attributes prior to creating it. Fortunately, tablespace is one of these attributes. Like with schema, all we need is to add a rule to remap old name into new - but for tablespace instead of schema names.

Normally SCOTT's objects reside in tablespace USERS. You can verify that by running SELECT below:

  SELECT segment_name,segment_type,tablespace_name FROM dba_segments WHERE owner='SCOTT';

If you want to use one of the existing tablespaces - note its name. Otherwise, for the purpose of this tutorial, let's create a small tablespace called USERS2:

  CREATE TABLESPACE users2 DATAFILE SIZE 500K;

In real life we'd also check that tables' owner has quota on the target tablespace, but since SCOTT has UNLIMITED TABLESPACE system privilege, we skip that. Finally, drop tables EMP and DEPT or set TABLE_EXISTS_ACTION to REPLACE as described in Importing existing tables section.

Prerequisites:
  1. SCOTT schema without tables EMP and DEPT.
  2. scott_tables.dmp we've created in export tutorial and located in DATA_PUMP_DIR
  3. USERS2 tablespace (created earlier in this tutorial) - or another tablespace of your choice

Demonstrated actions:

  1. Job Creation Wizard: Setting operation and mode, specifying input dump file, adding tablespace remap rule, creating the job.
  2. Main window: Job creation, job launching, progress log, job completion.

Cleanup:

  1. If you created tablespace USERS2, delete it (note that this drops scott's objects)
    DROP TABLESPACE users2 INCLUDING CONTENTS AND DATAFILES;
  2. Re-create schema SCOTT by running utlsampl.sql as described in How to create or fix sample schema should it become corrupted on tutorials main page.

Tips:

This step is common across our import tutorials: we IMPORT in TABLE mode.

Selecting operation IMPORT and mode TABLES on Operations And Mode tab

Step 2/4: Specifying input file

Like before, we are using file created by table mode export. We type its name "scott_tables.dmp" and select default directory DATA_PUMP_DIR. We then add dump file to the set:

Specifying scott_tables.dmp as input file

Step 3/4: Adding tablespace remap

This works similarly to schemas. Open Metadata Remaps page, in the dropdown box select TABLESPACE transform, specify USERS in "From" field, USERS2 in "To", click on "Add", and we are good to go.

Adding USERS -> USERS2 tablespace remap

Step 4/4: Running import

If you've chosen to REPLACE tables rather than dropping them, now is the best time to do that.

Job launch process is not much different from the ones we've seen in previous tutorials. It is not shown in the log, but tables were imported into USERS2 tablespace.

Creating and launching job. The job successfully completes.