Using Expdp & impdp and changing schemas with remap_schema and remap_tablespace

                                                                EXP/IMP Schema

On Source system:

• ·         Creating a directory object on target system

                SQL> create or replace directory dbexport AS '/mnt/PRDdata/imp_2408';

                Directory created.

                SQL> GRANT READ, WRITE ON DIRECTORY dbexport to System;

                Grant succeeded.

• Export process

·         expdp system/manager@LOGPROD schemas=STUDENT1 directory=dbexport dumpfile=STUDENT1.dmp logfile=expdpSTUDENT1.log

• ·         Schema student belongs to TRAINING TABLESPACE

SQL> select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='STUDENT1';

USERNAME                     DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------    ------------------------------                  ------------------------------

STUDENT1                        TRAINING                                      TEMP

On target system:-

• ·         Creating a directory object on target system

                SQL> create or replace directory dbexport AS '/mnt/PRDdata/imp_2408';

                Directory created.

• ·         Creating a new tablespace similar to the tablespace in Logitlity Production server.

CREATE TABLESPACE "TRAINING" DATAFILE

  '/mnt/PRDdata/oracle/oradata/logdev/training.dbf' SIZE 1048576000

  AUTOEXTEND ON NEXT 104857600 MAXSIZE 65535M

  LOGGING ONLINE PERMANENT BLOCKSIZE 16384

  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

• ·         Creating a new user

create user student1 identified by student1  default tablespace TRAINING temporary tablespace temp quota unlimited on TRAINING;

·         grant connect,resource to student1

·         grant create session, create any table to student1

• ·         now starting the import process again.

impdp system/manager@LOGDEV schemas=STUDENT1 directory=dbexport dumpfile=STUDENT1.dmp logfile=impdpSTUDENT1.log remap_schema=STUDENT1:STUDENT1 remap_tablespace=TRAINING:TRAINING