오라클 테이블 export import 방법 (expdp, impdp)

oralce DB의 테이블 데이터를 다른 oracle DB로 옮기고 싶을때가 있다.

물론 DB링크를 연결하는 것이 제일편하고 간단하지만

DB링크를 연결할수 없을때에는  export import를 이용하도록 한다.

사실 export import를 할때는 dmp파일이 서버에 생성되므로

파일시스템크기도 고려해야한다.

이외에 EXPORT/IMPORT를 백업으로 보기에는 무리가 있다.

이는 어디까지나 데이터 이관을 위한 유틸리티이며 백업의 안정성이나 정합성을 보장하지 않는다.

(보안에도 취약하다.)

백업은 별도의 솔루션이나 전통적으로 사용하던 begin/end backup을 이용한 hot backup이나

서비스를 내리고 진행하는 cold백업을 하여야 한다. 

1. 먼저 서버의 디렉토리를 oracle DB가 인식하도록 경로를 설정한다.

CREATE DIRECTORY TEST_DIRECTIRY as '/APP/oracle/oratest'; 

GRANT READ, WRITE ON DIRECTORY TEST_DIRECTIRY to system;

--디렉토리에 권한도 주자 

오라클에서 인식하는 디렉토리명은 TEST_DIRECTIRY이며

서버 경로는 /APP/oracle/oratest 으로 설정하였다.

SELECT * FROM dba_directories;  

--이쿼리를 통해 정상적으로 생성되었는지 확인가능하다.

vi 편집기로 아래 사항을 입력한다. (vi exp_TEST.par )

아래 예제는 XXEAC 사용자의 데이터를 모두 EXP_TEST.dmp 파일로 생성하고 TRIGGER는 제외한다는 의미이다.

USERID=system/manager DIRECTORY=TEST_DIRECTIRY JOB_NAME=EXP_TEST LOGFILE=EXP_TEST.log DUMPFILE=EXP_TEST.dmp SCHEMAS=XXEAC EXCLUDE=TRIGGER PARALLEL=2 CONTENT=ALL

이후 expdp parfile=exp_TEST.par 명령어로 진행하면 

아래 메시지를 띄우며 export가 진행된다.

Export: Release 11.2.0.3.0 - Production on Wed Feb 1 15:19:38 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."EXP_TEST":  system/******** parfile=exp_TEST.par  Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.125 MB 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/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY . . exported "XXEAC"."XXEPO_IPR_DAILY_RATES"             549.5 KB    5592 rows . . exported "XXEAC"."XXEPO_IPR_ITEM_CATEGORIES"         157.7 KB    1355 rows . . exported "XXEAC"."XXEPO_IPR_MTL_SYSTEM_ITEMS_B"      162.5 KB    1381 rows . . exported "XXEAC"."XXEA_USER_SIGN"                    7.718 KB      16 rows . . exported "XXEAC"."XXEA_FLEX_SETUP"                   9.609 KB       1 rows . . exported "XXEAC"."XXEA_FLEX_SETUP_DTL"               16.77 KB       2 rows . . exported "XXEAC"."XXEA_FLEX_TABLE"                   8.890 KB       4 rows . . exported "XXEAC"."XXEA_MENUS"                        8.312 KB       4 rows . . exported "XXEAC"."XXEA_MENUS_TL"                     8.375 KB       4 rows . . exported "XXEAC"."XXEA_MESSAGE"                      26.31 KB     206 rows . . exported "XXEAC"."XXEA_PROGRAMS"                     8.546 KB      17 rows . . exported "XXEAC"."XXEA_PROGRAMS_TL"                  9.078 KB      17 rows . . exported "XXEAC"."XXEA_QUICK_TYPE_HDR"               7.765 KB      15 rows . . exported "XXEAC"."XXEA_QUICK_TYPE_HDR_TL"            8.531 KB      15 rows . . exported "XXEAC"."XXEA_QUICK_TYPE_LINES"             14.23 KB      50 rows . . exported "XXEAC"."XXEA_QUICK_TYPE_LINES_TL"          10.36 KB      50 rows . . exported "XXEAC"."XXEA_SUB_MENUS"                    8.937 KB      17 rows . . exported "XXEAC"."XXEA_SUB_MENUS_TL"                 9.492 KB      17 rows . . exported "XXEAC"."XXEA_SYS_ITEM_DEF"                 8.726 KB       2 rows . . exported "XXEAC"."XXEA_SYS_ITEM_DEF_TL"              7.937 KB       2 rows . . exported "XXEAC"."XXEA_SYS_VALUE_HDR"                9.148 KB       2 rows . . exported "XXEAC"."XXEA_SYS_VALUE_HDR_TL"             8.351 KB       3 rows . . exported "XXEAC"."XXEA_TRX_TYPES"                    23.56 KB     257 rows . . exported "XXEAC"."XXEA_TRX_TYPES_TL"                 29.18 KB     257 rows . . exported "XXEAC"."XXEA_USERS"                        19.01 KB      18 rows . . exported "XXEAC"."XXEA_USER_SUB_MENUS"               14.65 KB     133 rows . . exported "XXEAC"."XXEPO_IPR_CURRENCIES"              27.88 KB     249 rows . . exported "XXEAC"."XXEPO_IPR_HR_LOCATIONS"            14.87 KB      16 rows . . exported "XXEAC"."XXEPO_IPR_MTL_ITEM_LOCATIONS"      15.23 KB      30 rows . . exported "XXEAC"."XXEPO_IPR_ORG_DEFINITIONS"         14.35 KB      11 rows . . exported "XXEAC"."XXEPO_IPR_PO_AGENTS"               14.13 KB      25 rows . . exported "XXEAC"."XXEPO_IPR_PO_VENDORS"              22.15 KB      91 rows . . exported "XXEAC"."XXEPO_IPR_PO_VENDOR_CONTACTS"      13.71 KB       1 rows . . exported "XXEAC"."XXEPO_IPR_PO_VENDOR_SITES"         26.96 KB      95 rows . . exported "XXEAC"."XXEPO_IPR_STOREROOM_LIST"          13.58 KB       3 rows . . exported "XXEAC"."XXEPO_IPR_UOMS"                     20.5 KB     107 rows . . exported "XXEAC"."FKL_AP_APPROVAL_INTERFACE"             0 KB       0 rows . . exported "XXEAC"."INTF_DEFINE_COL"                       0 KB       0 rows . . exported "XXEAC"."INTF_DEFINE_ID"                        0 KB       0 rows . . exported "XXEAC"."INTF_SOURCE_DATA"                      0 KB       0 rows . . exported "XXEAC"."INTF_SOURCE_EVENT"                     0 KB       0 rows . . exported "XXEAC"."INTF_TARGET_DATA"                      0 KB       0 rows . . exported "XXEAC"."INTF_TARGET_EVENT"                     0 KB       0 rows . . exported "XXEAC"."XXEA_APPRFLOWS"                        0 KB       0 rows . . exported "XXEAC"."XXEA_APPRFLOW_MNG"                     0 KB       0 rows . . exported "XXEAC"."XXEA_AP_INVOICES_ALL"                  0 KB       0 rows . . exported "XXEAC"."XXEA_AP_INVOICES_TRANS"                0 KB       0 rows . . exported "XXEAC"."XXEA_AP_INVOICE_LINES_ALL"             0 KB       0 rows . . exported "XXEAC"."XXEA_AP_INVOICE_LINES_TRANS"           0 KB       0 rows . . exported "XXEAC"."XXEA_AP_LOG"                           0 KB       0 rows . . exported "XXEAC"."XXEA_AP_PERIODS"                       0 KB       0 rows . . exported "XXEAC"."XXEA_AP_PRINT_ALL"                     0 KB       0 rows . . exported "XXEAC"."XXEA_ATTATCH"                          0 KB       0 rows . . exported "XXEAC"."XXEA_COA_VALUES"                       0 KB       0 rows . . exported "XXEAC"."XXEA_DEPT_TRX_TYPES"                   0 KB       0 rows . . exported "XXEAC"."XXEA_FLEX_TRX_DATA"                    0 KB       0 rows . . exported "XXEAC"."XXEA_FLEX_VALUES"                      0 KB       0 rows . . exported "XXEAC"."XXEA_LAST_NUM"                         0 KB       0 rows . . exported "XXEAC"."XXEA_SUPPLIERS_INT"                    0 KB       0 rows . . exported "XXEAC"."XXEA_SUPPLIER_BANKS_INT"               0 KB       0 rows . . exported "XXEAC"."XXEA_SYS_NOTICE"                       0 KB       0 rows . . exported "XXEAC"."XXEA_SYS_VALUE_LINES"                  0 KB       0 rows . . exported "XXEAC"."XXEA_TEMP_TRX_TYPE"                    0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_APPROVAL"                     0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_CURRFLOW_CTL"                 0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_CURRFLOW_DTL"                 0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_LINE_TYPES"                   0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_LINE_TYPES_TL"                0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_WORKFLOWS"                    0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_WORKFLOWS_MSG"                0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_WORKFLOW_CTL"                 0 KB       0 rows . . exported "XXEAC"."XXEA_TRX_WORKFLOW_DTL"                 0 KB       0 rows . . exported "XXEAC"."XXEA_USER_AGENT"                       0 KB       0 rows . . exported "XXEAC"."XXEA_USER_AGENT_DTL"                   0 KB       0 rows . . exported "XXEAC"."XXEA_USER_TRX_TYPES"                   0 KB       0 rows . . exported "XXEAC"."XXEA_WF_LISTS"                         0 KB       0 rows . . exported "XXEAC"."XXEA_WORKFLOWS"                        0 KB       0 rows . . exported "XXEAC"."XXEA_WORKFLOW_ACCT"                    0 KB       0 rows . . exported "XXEAC"."XXEA_WORKFLOW_CHANGE"                  0 KB       0 rows . . exported "XXEAC"."XXEA_WORKFLOW_ISSUE"                   0 KB       0 rows . . exported "XXEAC"."XXEPO_BUDGET_FUND_INQUIRY"             0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_ACTION_HISTORY"              0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_APPROVE_INFO"                0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_BUDGET_DEPARTMENT"           0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_BUDGET_FUND_INQUIRY"         0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_CANCEL_INFO"                 0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_CHARGE_COSTCENTER"           0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_EMPLOYEES"                   0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_FILE"                        0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_HEADERS"                     0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_IMPORT_STATUS"               0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_INTER_PLANT"                 0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_ITEM_PRICE"                  0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_LINES"                       0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_PO_STATUS"                   0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_REJECT_INFO"                 0 KB       0 rows . . exported "XXEAC"."XXEPO_IPR_RETURN_INFO"                 0 KB       0 rows . . exported "XXEAC"."XXEPO_PR_ITEM_PRICE"                   0 KB       0 rows Master table "SYSTEM"."EXP_TEST" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.EXP_TEST is:   /APP/oracle/oratest/EXP_TEST.dmp Job "SYSTEM"."EXP_TEST" successfully completed at 15:21:57

#만약 특정 테이블만 export하려면 아래로 생성한다.

USERID=system/manager DIRECTORY=TEST_DIRECTIRY JOB_NAME=EXP_TEST LOGFILE=EXP_TEST_TABLE.log DUMPFILE=EXP_TEST_TABLE.dmp TABLES=(XXEAC.XXEPO_IPR_INTER_PLANT, XXEAC.XXEPO_IPR_DAILY_RATES) EXCLUDE=TRIGGER PARALLEL=2 CONTENT=ALL

expdp parfile=exp_TEST.par 수행

#로그

Export: Release 11.2.0.3.0 - Production on Wed Feb 1 15:29:58 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."EXP_TEST":  system/******** parfile=exp_TEST.par  Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 640 KB . . exported "XXEAC"."XXEPO_IPR_DAILY_RATES"             549.5 KB    5592 rows . . exported "XXEAC"."XXEPO_IPR_INTER_PLANT"                 0 KB       0 rows Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Master table "SYSTEM"."EXP_TEST" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.EXP_TEST is:   /APP/oracle/oratest/EXP_TEST_TACLE.dmp Job "SYSTEM"."EXP_TEST" successfully completed at 15:30:18

#이제 import를 해보자

impdp system/manager file=EXP_TEST_TABLE.dmp  fromuser=xxeac touser=tstuser log=test_imp.log

#진행로그

Import: Release 11.2.0.3.0 - Production on Wed Feb 1 16:01:55 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "file=EXP_TEST_TACLE.dmp" Location: Command Line, Replaced with: "dumpfile=TEST_DIRECTIRY:EXP_TEST_TACLE.dmp" Database Directory Object "TEST_DIRECTIRY" has been added to file specification: "EXP_TEST_TACLE.dmp". Legacy Mode Parameter: "fromuser=xxeac" Location: Command Line, Replaced with: "remap_schema" Legacy Mode Parameter: "log=test_imp.log" Location: Command Line, Replaced with: "logfile=TEST_DIRECTIRY:test_imp.log" Database Directory Object "TEST_DIRECTIRY" has been added to file specification: "test_imp.log". Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=TEST_DIRECTIRY:EXP_TEST_TACLE.dmp remap_schema=xxeac:tstuser logfile=TEST_DIRECTIRY:test_imp.log  Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "tstuser"."XXEPO_IPR_DAILY_RATES"               549.5 KB    5592 rows . . imported "tstuser"."XXEPO_IPR_INTER_PLANT"                   0 KB       0 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 16:02:01

#이후 확인

select * from dba_segments where owner='tstuser'

로 확인해보면 테이블뿐만 아니라 인덱스도 같이 이관되었다.

*중요: 참고로 export하려는 table에 oracle TDE(오라클 암호화솔루션)가 설정되어있다면 export는 불가능하다.

아래 에러화면을 예로 설명하겠다.

exp apps/******** tables=테이블명   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in UTF8 character set and AL16UTF16 NCHAR character set   About to export specified tables via Conventional Path ... EXP-00111: Table 테이블명 resides in an Encrypted Tablespace 테이블스페이스   and will not be exported Export terminated successfully with warning

#만약 import 하려는 테이블에 다른유저로 넣으려면 어떻게할까?

아래를 참고하자

expdp system/manager DIRECTORY= TEST_DIRECTIRY tables=APPLSYS.TEST_TABLE DUMPFILE=TST01.dmp

--위 명령어로 export 했다고 가정하자 applsys유저로 받았지만

import는 test01사용자로 하고싶다면 아래처럼 진행하자

impdp system/manager DIRECTORY= TEST_DIRECTIRY remap_schema=applsys:test01 tables=APPLSYS.TEST_TABLE DUMPFILE=TST01.dmp

--에러가 없다면 성공!

remap_schema 를 통해 owner를 변경할수 있다는것이다!