Using Expdp & impdp and changing schemas with remap_schema

Posted by pparkko on May 16, 2011

What is really great about expdp & impdp is that you can export and import to new schemas very easily. That needed some work earlier with export and import.

Let’s say that I want to import into HR_NEW schema instead of HR.

We just add a remap_schema into the import command: remap_schema=HR:HR_NEW

This will actually even create the new user (schema)!

C:\Temp>impdp system/oracle dumpfile=hr.dmp directory=dmpdir schemas=hr logfile=
hr_imp.log remap_schema=HR:HR_NEW

Import: Release 11.2.0.2.0 – Beta on Ma Touko 16 11:42:28 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 – Beta
Master table “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_01″:  system/******** dumpfile=hr.dmp direc
tory=dmpdir schemas=hr logfile=hr_imp.log remap_schema=HR:HR_NEW
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “HR_NEW”.”COUNTRIES”                        6.367 KB      25 rows
. . imported “HR_NEW”.”DEPARTMENTS”                      7.007 KB      27 rows
. . imported “HR_NEW”.”EMPLOYEES”                        16.80 KB     107 rows
. . imported “HR_NEW”.”JOB_HISTORY”                      7.054 KB      10 rows
. . imported “HR_NEW”.”JOBS”                             6.992 KB      19 rows
. . imported “HR_NEW”.”LOCATIONS”                        8.273 KB      23 rows
. . imported “HR_NEW”.”REGIONS”                          5.476 KB       4 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/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_SCHEMA_01″ successfully completed at 11:42:47

The new user is still locked:
SQL> connect hr_new/hr_new
ERROR:
ORA-28000: the account is locked

Unlock:
C:\Temp>sqlplus system/oracle

SQL*Plus: Release 11.2.0.2.0 Beta on Ma Touko 16 11:44:30 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 – Beta

SQL> alter user hr_new account unlock;

User altered.

SQL> alter user hr_new identified by hr_new;

User altered.

SQL> connect hr_new/hr_new
Connected.

SQL> select count(*) as amount, object_type
  2  from user_objects
  3  group by object_type;

    AMOUNT OBJECT_TYPE
———- ——————-
3 SEQUENCE
         2 PROCEDURE
         2 TRIGGER
         7 TABLE
        19 INDEX
         1 VIEW

6 rows selected.

Sometimes you need to also remap_tablespaces with … remap_tablespace. What a tool!

That was of course possible with exp & imp. But since it was not supported I remember setting quotas to 0 and changing default tablespaces to be able to import to different tablespace..

Hope this helps,
Pasi