Data Pump IMPDP REMAP_TABLESPACE Parameter Syntax and Examples

Purpose of this option is to import the objects mentioned in the source tablespace will be imported into the target tablespace. REMAP_TABLESPACE is a good option and it is very useful when the target database does not have the same set of tablespaces. Also, if you wanted to import the dump content to another tablespace which is having more free space then this option is helpful.

Syntax:

Impdp REMAP_TABLESPACE=source_tablespace_name:target_tablespace_name

In a single impdp command you can mention more than one remap_tablespace parameters can be specified. But the source tablespace name must be different. You have to allocate proper quota space to the user in the target tablespaces.

Example

I am taking scott user with DATA as default tablespace and all the objects in the scott schema is in DATAtablespace.

SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME ='SCOTT';

DEFAULT_TABLESPACE

-------------------

DATA

SQL> select table_name, tablespace_name from dba_tables where owner='SCOTT';

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

S_APLT_WTI_INTL                DATA

S_APPL_WTMPL_IT                DATA

CANDIDATE                      DATA

CX_KPN_FLITS                   DATA

DEPTREE_TEMPTAB                DATA

EMP                            DATA

TEMP_EMP

DEPT                           DATA

EXAMPLE_SHRINK                 DATA

9 rows selected.

Exporting the entire schema

$ expdp schemas=scott directory=exp_dir dumpfile=remap.dmp logfile=remap.log compression=all

Export: Release 11.2.0.2.0 - Production on Fri May 11 03:05:31 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=scott directory=exp_dir dumpfile=remap.dmp logfile=remap.log compressi           on=all

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 860.2 MB

. . .

. . .

. . .

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

  /home/oracle/shony/remap.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:06:45

I am going to drop the user and recreate it without any object.

SQL> connect / as sysdba

Connected.

SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by voyage123 default tablespace data temporary tablespace temp;

User created.

SQL> alter user scott quota unlimited on data;

User altered.

I have created a new tablespace called remap_test and I am going grant quota to the new scott user.

SQL> alter user scott quota unlimited on remap_test;

User altered.

SQL> grant connect, resource to scott;

Grant succeeded.

Now I am going to import the previous dump with remap_tablespace option

$ impdp directory=exp_dir dumpfile=remap.dmp logfile=imp_remap.logremap_tablespace=data:remap_test

Import: Release 11.2.0.2.0 - Production on Fri May 11 05:22:44 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=exp_dir dumpfile=remap.dmp logfile=imp_remap.log remap_tablespace=data:remap_test

. . .

. . .

. . .

Job "SYS"."SYS_IMPORT_FULL_01" completed successfully at 05:23:40

After the import if you check the objects tablespace as REMAP_TEST. Even though it is export from DATA tablespace.

SQL> select table_name, tablespace_name from dba_tables where owner='SCOTT';

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

S_APLT_WTI_INTL                REMAP_TEST

S_APPL_WTMPL_IT                REMAP_TEST

CANDIDATE                      REMAP_TEST

CX_KPN_FLITS                   REMAP_TEST

DEPTREE_TEMPTAB                REMAP_TEST

EMP                            REMAP_TEST

TEMP_EMP

DEPT                           REMAP_TEST

EXAMPLE_SHRINK                 REMAP_TEST

9 rows selected.