오라클에서 데이터 이관 시 가장 많이 사용하는 방법이 data pump라고 생각합니다
소스와 타겟의 테이블 형상이 다른 경우 datapump는 어떻게 작동하는지 테스트 하였습니다
테스트 준비
tab1: 원본
tab2: 컬럼 없음
tab3: 컬럼 많음
tab4: 컬럼 순서 다름
> CREATE TABLE sk.tab1 AS (SELECT owner, object_name, object_id, object_type FROM dba_objects)
73399 row(s) modified.
> CREATE TABLE sk.tab2 AS (SELECT owner, object_name, object_id FROM dba_objects WHERE 1=0)
0 row(s) modified.
> CREATE TABLE sk.tab3 AS (SELECT owner, object_name, object_id, object_type, created FROM dba_objects WHERE 1=0)
0 row(s) modified.
> CREATE TABLE sk.tab4 AS (SELECT object_name, owner, object_type, object_id FROM dba_objects WHERE 1=0)
0 row(s) modified.
원본 테이블 export
[ora19ent@svr pump]$ expdp system/oracle DUMPFILE=tab1.dmp TABLES=sk.tab1 DIRECTORY=PUMP_DIR CONTENT=DATA_ONLY
Export: Release 19.0.0.0.0 - Production on Mon Sep 16 11:34:03 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** DUMPFILE=tab1.dmp TABLES=sk.tab1 DIRECTORY=PUMP_DIR CONTENT=DATA_ONLY
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . exported "SK"."TAB1" 4.004 MB 73399 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/ora19ent/pump/tab1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Sep 16 11:34:08 2024 elapsed 0 00:00:03
[ora19ent@svr pump]$
> SELECT * FROM sk.tab1 WHERE rownum <= 10
OWNER|OBJECT_NAME |OBJECT_ID|OBJECT_TYPE|
-----+--------------+---------+-----------+
SYS |TS$ | 16|TABLE |
SYS |ICOL$ | 20|TABLE |
SYS |C_FILE#_BLOCK#| 8|CLUSTER |
SYS |I_OBJ2 | 37|INDEX |
SYS |USER$ | 22|TABLE |
SYS |I_TAB1 | 33|INDEX |
SYS |I_OBJ5 | 40|INDEX |
SYS |CDEF$ | 31|TABLE |
SYS |I_IND1 | 41|INDEX |
SYS |I_OBJ# | 3|INDEX |
10 row(s) fetched.
상황별 import 테스트
소스에 있는 컬럼 타겟 테이블에 없음 경우
타겟에 없는 컬럼 제외 후 import 완료
소스에 없는 컬럼 타겟 테이블에 있는 경우
[ora19ent@svr pump]$ impdp system/oracle DIRECTORY=PUMP_DIR DUMPFILE=tab1.dmp REMAP_TABLE=tab1:tab2 table_exists_action=TRUNCATE
Import: Release 19.0.0.0.0 - Production on Mon Sep 16 11:37:01 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=PUMP_DIR DUMPFILE=tab1.dmp REMAP_TABLE=tab1:tab2 table_exists_action=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB2" 4.004 MB 73399 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Sep 16 11:37:04 2024 elapsed 0 00:00:03
[ora19ent@svr pump]$
> SELECT * FROM sk.tab2 WHERE rownum <= 10
OWNER|OBJECT_NAME |OBJECT_ID|
-----+--------------+---------+
SYS |TS$ | 16|
SYS |ICOL$ | 20|
SYS |C_FILE#_BLOCK#| 8|
SYS |I_OBJ2 | 37|
SYS |USER$ | 22|
SYS |I_TAB1 | 33|
SYS |I_OBJ5 | 40|
SYS |CDEF$ | 31|
SYS |I_IND1 | 41|
SYS |I_OBJ# | 3|
10 row(s) fetched.
타겟에만 있는 컬럼 빼고 import 완료
소스, 타겟 컬럼 순서 다른 경우
[ora19ent@svr pump]$ impdp system/oracle DIRECTORY=PUMP_DIR DUMPFILE=tab1.dmp REMAP_TABLE=tab1:tab3 table_exists_action=TRUNCATE
Import: Release 19.0.0.0.0 - Production on Mon Sep 16 11:37:55 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=PUMP_DIR DUMPFILE=tab1.dmp REMAP_TABLE=tab1:tab3 table_exists_action=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB3" 4.004 MB 73399 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Sep 16 11:37:56 2024 elapsed 0 00:00:01
[ora19ent@svr pump]$
> SELECT * FROM sk.tab3 WHERE rownum <= 10
OWNER|OBJECT_NAME |OBJECT_ID|OBJECT_TYPE|CREATED|
-----+--------------+---------+-----------+-------+
SYS |TS$ | 16|TABLE | |
SYS |ICOL$ | 20|TABLE | |
SYS |C_FILE#_BLOCK#| 8|CLUSTER | |
SYS |I_OBJ2 | 37|INDEX | |
SYS |USER$ | 22|TABLE | |
SYS |I_TAB1 | 33|INDEX | |
SYS |I_OBJ5 | 40|INDEX | |
SYS |CDEF$ | 31|TABLE | |
SYS |I_IND1 | 41|INDEX | |
SYS |I_OBJ# | 3|INDEX | |
10 row(s) fetched.
컬럼 순서가 달라도 알아서 찾아 들어감
[ora19ent@svr pump]$ impdp system/oracle DIRECTORY=PUMP_DIR DUMPFILE=tab1.dmp REMAP_TABLE=tab1:tab4 table_exists_action=TRUNCATE
Import: Release 19.0.0.0.0 - Production on Mon Sep 16 11:38:03 2024
Version 19.13.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DIRECTORY=PUMP_DIR DUMPFILE=tab1.dmp REMAP_TABLE=tab1:tab4 table_exists_action=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SK"."TAB4" 4.004 MB 73399 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Sep 16 11:38:04 2024 elapsed 0 00:00:01
[ora19ent@svr pump]$
> SELECT * FROM sk.tab4 WHERE rownum <= 10
OBJECT_NAME |OWNER|OBJECT_TYPE|OBJECT_ID|
--------------+-----+-----------+---------+
TS$ |SYS |TABLE | 16|
ICOL$ |SYS |TABLE | 20|
C_FILE#_BLOCK#|SYS |CLUSTER | 8|
I_OBJ2 |SYS |INDEX | 37|
USER$ |SYS |TABLE | 22|
I_TAB1 |SYS |INDEX | 33|
I_OBJ5 |SYS |INDEX | 40|
CDEF$ |SYS |TABLE | 31|
I_IND1 |SYS |INDEX | 41|
I_OBJ# |SYS |INDEX | 3|
10 row(s) fetched.
댓글 쓰기