advertisements
_____________________________________________________________________________________________________________________
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment