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 

How to Export DB_LINKS

We used to refresh our DEV database from production.so we need to preserve the DEV dblinks before we start refresh . we need to restore after refresh.

We can export the all the dblinks by using expdp.

Please create a para.par file with below commands.

full=y
INCLUDE=DB_LINK:"IN(SELECT db_link FROM dba_db_links)"

Then execute below command to export

expdp "'/as sysdba'"  directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink-restore_JAYDEV.log parfile=para.par


After Refresh,Please execute below impdp command to recreate dblinks.

Note: DBLINK we are going to import should not exist in the target database(DEV).please drop dblinks if it already exists.

impdp "'/as sysdba'" directory=DATA_PUMP_DIR dumpfile=dblink-restore_JAYDEV.dmp logfile=dblink_restore_JAYDEV.log


ALTERNATE METHOD:

Before Refresh,We can take ddl of dblinks  by using below query and spool it. Then we can connect to each schema and recreate the dblinks after refresh.

set long 100000 head off
spool exp_dblinks.dat
SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'PUBLIC') FROM DBA_DB_LINKS WHERE OWNER = 'PUBLIC';


SELECT DBMS_METADATA.GET_dDL('DB_LINK', DB_LINK,'XXRA') FROM DBA_DB_LINKS WHERE OWNER = 'JAY';