10g - DATAPUMP IMPORT 는 자동으로 USER 생성

출처 :  https://kr.forums.oracle.com/forums/thread.jspa?threadID=477157&tstart=75

PURPOSE

다음은 DATAPUMP의 기능을 소개하고자 한다.




Explanation

imp utility 에서는 target user가 존재해야 했었다.
그러나 DATAPUMP 는 만익 target user가 존재하지 않는다면 이를 자동으로 생성한다.




Example

Source database 에서 TEST라는 user 가 있다.
TEST ( password : test , role : connect , resource )

Export the TEST schema using datapump:
expdp system/oracle dumpfile=exp.dmp schemas=TEST


Case I
=======
test user가 존재하지 않을 경우에 import 는 test user를 자동으로 생성한다.

impdp system/oracle dumpfile=exp.dmp

*************TEST does not exist*************************************
Import: Release 10.1.0.2.0 - Production on Friday, 28 May, 2004 1:02

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Data Mining option
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=exp.dmp
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/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT" 5.648 KB 4 rows
. . imported "TEST"."SALGRADE" 5.648 KB 10 rows
. . imported "TEST"."BONUS" 0 KB 0 rows
. . imported "TEST"."EMP" 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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 01:02
***********************************************************************

connect TEST/TEST (on target database)
=> connected
SQL> select * from session_roles;
ROLE
---------
connect
resource






Case II
========
Target database 에 TEST user가 존재하는 경우에 warning message가 발생하며 import 작업은 계속 진행된다.

impdp system/oracle dumpfile=exp.dmp

*************user TEST already exists************************************
Import: Release 10.1.0.2.0 - Production on Friday, 28 May, 2004 1:06

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Data Mining option
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=exp.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
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/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT" 5.648 KB 4 rows
. . imported "TEST"."SALGRADE" 5.648 KB 10 rows
. . imported "TEST"."BONUS" 0 KB 0 rows
. . imported "TEST"."EMP" 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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 01:06
*************************************************************************

You will receive ORA-31684 error but import will continue.






Case - III
===========
Target database에 TEST user가 존재하지만 warning (ora-31684) 을 피하기 위해서는
EXCLUDE=USER 를 사용한다.

impdp system/oracle dumpfile=exp.dmp exclude=user

*********Disable create user statment as user TEST already exist***********
Import: Release 10.1.0.2.0 - Production on Friday, 28 May, 2004 1:11

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Data Mining option
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=exp.dmp exclud
e=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/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT" 5.648 KB 4 rows
. . imported "TEST"."SALGRADE" 5.648 KB 10 rows
. . imported "TEST"."BONUS" 0 KB 0 rows
. . imported "TEST"."EMP" 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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 01:11

****************************************************************************

TEST user가 존재하지 않는데 EXCLUDE=USER parameter 를 사용하면 ORA-01917 error 를 만나게 된다.




Reference Documents

Note : 272132.1 DATAPUMP import automatically creates schema