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.

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:

• Tablespace remap can be specified individually for each object type. This allows things like separating tables and indexes into different tablespaces, or putting materialized views to designated place.
• The previous tip sounded too good to be true and it is: the functionality was broken in version prior to 10.2.0.4 patch 17 or Oracle 11.1. Buggy versions honor only the first remap rule, the workaround was to break import into two or more steps by limiting object types and specifying tablespace names for that type. Sounds like old days of imp's reign? Pity one of the best Data Pump features got overlooked for such long.

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

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:

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.

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.