IMPDP: REMAP_SCHEMA creates new schema?

Hello,

Sometimes we tend to miss some simple thing which I had similar in expdp and impdp.

Normally when you want to export a schema and import into another schema we user fromuser and touser, before to that you have to create the user in target database where you are going to import.

In expdp/impdp also you have remap_schema for the same purpose, but you dont need to create the target schema there.
IMPDP itself creates target schema for you but the schema will be locked and you have to set the password before you start using it.

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)!

Thanks to my colleague (Swapna) pointing out to me that it is possible, infact the following is her work really.

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 - Jan 03 11:42:28 2013
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  
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01? successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01?: system/******** dumpfile=hr.dmp 
directory=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 locked as said.

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 Jan 03 11:42:28 2013 11:44:30 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0
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.

Same case with remap_tablespaces with remap_tablespace.
-Thanks
Geek DBA