impdp remap_schema

How to export and import the schema from one user to another ?

Here I'm going to explain how to use impdp/expdp utility to import schema and export it to another user.

Getting Started

Create a directory object in database that point to a physical location on the Operating system where dumps,log etc are going to store.

CREATE OR REPLACE DIRECTORY java_latte_dir AS '/home/oracle/java_latte' ;

This directory object is only a pointer to a physical directory, it will not create any directory.

You can view all the directories with the help of DBA_DIRECTORIES view.

Those users who are going to use these utility must have READ,WRITE permission to this directory object.

GRANT READ OR WRITE ON DIRECTORY to SCOTT;

Schema Import/Export

Here we first try to export a schema of user TEST using the following expdp command.

expdp scott/tiger schemas=TEST directory=java_late_dir dumpfile=java_latte.dmp logfile=java_latte.log

Output  is look like this :

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA schemas=test directory=TEST1_DIR dumpfile=test.dmp logfile=test.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 55.12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."BIG_TABLE"                          46.61 MB 1000000 rows
. . exported "TEST"."ABC"                                5.062 KB       1 rows
. . exported "TEST"."LOOKUP"                             5.492 KB       3 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2005"            0 KB       0 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2006"            0 KB       0 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2007"            0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/java_latte/java_latte.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:56:15

Now we try to import this schema to different user. There is no need of creating another user, it will automatically create the new user.

impdp scott/tiger dumpfile=java_latte.dmp directory=java_late_dir remap_schema=TEST:TEST1

Output look like this:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  sys/******** AS SYSDBA dumpfile=test.dmp directory=TEST1_DIR remap_schema=TEST:TEST3 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST3"."BIG_TABLE"                         46.61 MB 1000000 rows
. . imported "TEST3"."ABC"                               5.062 KB       1 rows
. . imported "TEST3"."LOOKUP"                            5.492 KB       3 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2005"           0 KB       0 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2006"           0 KB       0 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2007"           0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:13:01

Finally, you need to unlock the user and set the password for them.

Connect as sys user and use the following command :

ALTER USER test1 ACCOUNT UNLOCK;

ALTER USER test1 IDENTIFIED BY test1;

if you find this information useful, please comment.