Database.fi

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

Tags: expdp, export, impdp, import, Oracle, remap_schema

This entry was posted on Monday, May 16th, 2011 at 09:00 and is filed under Oracle, expdp and impdp. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.