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 DATA tablespace.
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.log remap_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.

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google