IMPDP

Moving data from one tablespace to new tablespace:

1)      Take a schema level export of particular user

è Expdp sarathi/sarathi123 dumpfile=<file.dmp> logfile=<file.log>  schemas=sbbjprod directory=data

2)      Create a new tablespace 

è Create  tablespace <tablespace name> datafile= ‘<location whare you want’> size 31G

3)      Create a new user and assign a   new tablespace as a default  tablespace

è Create user  <username>  default tablespace <new tablespace name>

4)      Import the data

Impdp sbjprod/sbjprod dumpfile=<filename.dmp> logfile=<file.log>  remap_tablespace=users:sbbj remap_schema=sbbjprod:sbjprod directory=data

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 anand/anand dumpfile=scott.dmp logfile=scott1.log remap_tablespace=users:sbh  remap_schema=scott:anand

Or

$ 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.