Import Schema from one Tablespace to another Tablespace

We had a requirement to move schema from one tablespace to another.We can achieve this using expdp & impdp.

Current tablespace       New Tablespace

IAM_LOB                    JAY_LOB
IAM_TABLE               JAY_TABLE
IAM_INDEX               JAY_INDEX

EXPORT SCHEMA USING EXPDP:

Create a parameter file with below parameters
oracle:JAYDEV> more para.par
DIRECTORY=JAY_DIR
SCHEMAS=JAY
DUMPFILE=JAY_EXPORT_%U.dmp
FILESIZE=8G
LOGFILE=EXPORT_JAY.log
JOB_NAME=JAY_EXPORT


expdp "'/as sysdba'" parfile=para.par




SQL> select tablespace_name from dba_ts_quotas where username='JAY';

TABLESPACE_NAME
------------------------------
IAM_LOB
IAM_TABLE
IAM_INDEX

select count(*) from dba_objects where owner='JAY';

 COUNT(*)
----------
       846

Now drop the user jay & tablespaces.

drop user jay cascade;
drop the old tablespaces.


SQL> select count(*) from dba_tables where tablespace_name like 'IAM%';

  COUNT(*)
----------
         0

SQL> select count(*) from dba_segments where tablespace_name like 'IAM%';

  COUNT(*)
----------
         0

SQL>  select count(*) from dba_indexes  where tablespace_name like 'IAM%';

  COUNT(*)
----------

         0

SQL> DROP TABLESPACE IAM_INDEX INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE IAM_LOB INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE IAM_TABLE INCLUDING CONTENTS AND DATAFILES;


Tablespace dropped.


IMPORT SCHEMA USING IMPDP (remap_tablespace):

Create a parameter file with below parameters .


oracle:JAYDEV> more imp.par
DIRECTORY=JAY_DIR
DUMPFILE=JAY_EXPORT_%U.dmp
REMAP_TABLESPACE=IAM_TABLE:JAY_TABLE,IAM_INDEX:JAY_INDEX,IAM_LOB:JAY_LOB
LOGFILE=IMPORT_VEMTEST.log
JOB_NAME=JAY_IMPORT

impdp "'/as sysdba'" parfile=imp.par

verify the object_count