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.
DROP TABLESPACE users2 INCLUDING CONTENTS AND DATAFILES;
This step is common across our import tutorials: we IMPORT in TABLE mode.
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:
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.
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.
© 2007-2012 AlderProgs Consulting Ltd.