조회 수 16784 추천 수 0 댓글 0

[10G] DATAPUMP IMPORT는 자동으로 USER 생성
===================================

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

 

 

 

from otn


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84911
40 Tip 오라클 업그레이드 메뉴얼 1 file perfstat 2012.02.06 11894
39 Tip LIBRARY CACHE PIN 고구마 2011.11.10 12949
38 Tip ORACLE 10G CONVERTING TABLESPACES WITH THE RMAN CONVERT 1 흑수건 2011.10.20 11949
37 Tip 11g alertlog 1 흑수건 2011.10.06 14423
36 Tip DATABASE에 CONNECT안될때 SYSTEMSTATE DUMP하는법 흑수건 2011.10.06 10657
» Tip [10g]DATAPUMP IMPORT는 자동으로 USER 생성 흑수건 2011.10.06 16784
34 Tip [11g] New Feature : Invisible Index 1 흑수건 2011.10.06 11357
33 Tip EXPORT의 Query Option 기능 흑수건 2011.10.01 11840
32 Tip 특정 DB USER의 SESSION수를 제한하는 방법 흑수건 2011.10.01 12391
31 Tip 오라클상세 버젼 확인하기 lsinventory 고구마 2011.09.14 13847
30 Tip Windows 32bit OS 메모리 제약과 Oracle 에서 추가메모리 사용하도록 흑수건 2011.09.04 15775
29 Tip SESSIONS: derived (1.1 * PROCESSES + 5) 흑수건 2011.08.30 11322
28 Tip cpu 사용이 높을때 흑수건 2011.08.30 11489
27 Tip hardparse 발생및 세션 검사 2 도로시 2011.08.17 12666
26 Tip ora-3113 에러 분석 접근관련... 고구마 2011.06.14 12790
25 Tip 오라클 제품 비교 file 송기성 2011.05.22 13417
24 자료 oracle 문제발생시 대처 가이드 2 file dbkill 2011.05.16 5652
23 자료 ORA-600 metalink notes for help file dbkill 2011.03.29 7641
22 자료 C컴파일시 makefile의 구조설명 file 김준호 2010.04.22 7231
21 자료 윈도우 tail 사용법 1 file 유주환 2010.04.19 7374
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8