오라클 19c expdp master_table 옵션

OS 환경 : Oracle Linux 8.7 (64bit)

 

DB 환경 : Oracle Database 19.27.0.0

 

방법 : 오라클 19c expdp master_table 옵션

오라클 19c의 datapump(expdp/impdp)에서는 datapump를 사용할때 생기는 마스터 테이블을 datapump 작업이 끝나고도 지워지지 않게하는 옵션이 존재함
옵션은 keep_master로 기본이 n임, y로 변경시 datapump 수행시 보이는 "SYSTEM"."SYS_EXPORT_FULL_01" 과 같은 테이블이 지워지지 않음
본문에서는 해당 옵션을 사용해서 실제로 마스터테이블이 남는지 확인해봄

 

 

테스트
디렉토리 생성

1 2 3

SQL> create directory mig_dir as '/oraimsi/datapump';   Directory created.

 

 

expdp 실행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175

$ expdp system/oracle directory=mig_dir dumpfile=20251029_full.dmp logfile=20251029_full.log keep_master=y full=y   Export: Release 19.0.0.0.0 - Production on Wed Oct 29 22:35:28 2025 Version 19.27.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_FULL_01":  system/******** directory=mig_dir dumpfile=20251029_full.dmp logfile=20251029_full.log keep_master=y full=y Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/STATISTICS/MARKER Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/ROLE Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.937 KB      29 rows . . exported "SYSTEM"."REDO_DB"                          25.59 KB       1 rows . . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows . . exported "WMSYS"."WM$HINT_TABLE$"                    9.992 KB      97 rows . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$"          7.085 KB      11 rows . . exported "SYS"."DAM_CONFIG_PARAM$"                   6.531 KB      14 rows . . exported "SYS"."TSDP_SUBPOL$"                        6.328 KB       1 rows . . exported "WMSYS"."WM$NEXTVER_TABLE$"                 6.382 KB       1 rows . . exported "WMSYS"."WM$ENV_VARS$"                      6.023 KB       3 rows . . exported "SYS"."TSDP_PARAMETER$"                     5.953 KB       1 rows . . exported "SYS"."TSDP_POLICY$"                        5.921 KB       1 rows . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$"       5.984 KB       1 rows . . exported "WMSYS"."WM$EVENTS_INFO$"                   5.820 KB      12 rows . . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows . . exported "SYS"."AUD$"                                    0 KB       0 rows . . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows . . exported "SYS"."DAM_CLEANUP_JOBS$"                       0 KB       0 rows . . exported "SYS"."TSDP_ASSOCIATION$"                       0 KB       0 rows . . exported "SYS"."TSDP_CONDITION$"                         0 KB       0 rows . . exported "SYS"."TSDP_FEATURE_POLICY$"                    0 KB       0 rows . . exported "SYS"."TSDP_PROTECTION$"                        0 KB       0 rows . . exported "SYS"."TSDP_SENSITIVE_DATA$"                    0 KB       0 rows . . exported "SYS"."TSDP_SENSITIVE_TYPE$"                    0 KB       0 rows . . exported "SYS"."TSDP_SOURCE$"                            0 KB       0 rows . . exported "SYSTEM"."REDO_LOG"                             0 KB       0 rows . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$"         0 KB       0 rows . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$"                 0 KB       0 rows . . exported "WMSYS"."WM$CONS_COLUMNS$"                      0 KB       0 rows . . exported "WMSYS"."WM$LOCKROWS_INFO$"                     0 KB       0 rows . . exported "WMSYS"."WM$MODIFIED_TABLES$"                   0 KB       0 rows . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$"         0 KB       0 rows . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$"        0 KB       0 rows . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$"              0 KB       0 rows . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$"          0 KB       0 rows . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$"                 0 KB       0 rows . . exported "WMSYS"."WM$RIC_TABLE$"                         0 KB       0 rows . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$"                0 KB       0 rows . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$"             0 KB       0 rows . . exported "WMSYS"."WM$UDTRIG_INFO$"                       0 KB       0 rows . . exported "WMSYS"."WM$VERSION_TABLE$"                     0 KB       0 rows . . exported "WMSYS"."WM$VT_ERRORS_TABLE$"                   0 KB       0 rows . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$"        0 KB       0 rows . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.960 KB       2 rows . . exported "SYS"."DBA_SENSITIVE_DATA"                      0 KB       0 rows . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"              0 KB       0 rows . . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows . . exported "SYS"."NACL$_ACE_EXP"                           0 KB       0 rows . . exported "SYS"."NACL$_HOST_EXP"                      6.921 KB       1 rows . . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows . . exported "SYS"."SQL$TEXT_DATAPUMP"                       0 KB       0 rows . . exported "SYS"."SQL$_DATAPUMP"                           0 KB       0 rows . . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP"                 0 KB       0 rows . . exported "SYS"."SQLOBJ$DATA_DATAPUMP"                    0 KB       0 rows . . exported "SYS"."SQLOBJ$PLAN_DATAPUMP"                    0 KB       0 rows . . exported "SYS"."SQLOBJ$_DATAPUMP"                        0 KB       0 rows . . exported "SYSTEM"."SCHEDULER_JOB_ARGS"                   0 KB       0 rows . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"               0 KB       0 rows . . exported "WMSYS"."WM$EXP_MAP"                        7.718 KB       3 rows . . exported "WMSYS"."WM$METADATA_MAP"                       0 KB       0 rows . . exported "SYSTEM"."ALL_TABLE_STATS"                  378.0 MB 2814157 rows . . exported "SYSTEM"."ALL_TABLE_STATS2"                 223.6 MB 1529704 rows . . exported "SYSTEM"."STAT_TAB"                         3.624 MB   25700 rows . . exported "SYSTEM"."SAMPLE_1K"                        21.97 KB    1000 rows . . exported "IMSI"."EMPLOYEE"                           17.10 KB     107 rows . . exported "IMSI"."EMPLOYEES"                          17.10 KB     107 rows . . exported "IMSI"."LOCATION"                           8.445 KB      23 rows . . exported "IMSI"."LOCATIONS"                          8.445 KB      23 rows . . exported "IMSI"."JOB"                                7.109 KB      19 rows . . exported "IMSI"."JOBS"                               7.109 KB      19 rows . . exported "IMSI"."DEPARTMENT"                         7.132 KB      27 rows . . exported "IMSI"."DEPARTMENTS"                        7.132 KB      27 rows . . exported "IMSI"."REGION"                             5.554 KB       4 rows . . exported "IMSI"."REGIONS"                            5.554 KB       4 rows . . exported "IMSI"."CHANNELS"                               0 KB       0 rows . . exported "IMSI"."COUNTRIES"                              0 KB       0 rows . . exported "IMSI"."COUNTRY"                                0 KB       0 rows . . exported "IMSI"."CUSTOMERS"                              0 KB       0 rows . . exported "IMSI"."JOB_HISTORY"                            0 KB       0 rows . . exported "IMSI"."PRODUCTS"                               0 KB       0 rows . . exported "IMSI"."PROMOTIONS"                             0 KB       0 rows . . exported "IMSI"."SALES":"SALES_1995"                     0 KB       0 rows . . exported "IMSI"."SALES":"SALES_1996"                     0 KB       0 rows . . exported "IMSI"."SALES":"SALES_H1_1997"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_H2_1997"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q1_1998"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q1_1999"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q1_2000"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q1_2001"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q1_2002"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q1_2003"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q2_1998"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q2_1999"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q2_2000"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q2_2001"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q2_2002"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q2_2003"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q3_1998"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q3_1999"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q3_2000"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q3_2001"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q3_2002"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q3_2003"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q4_1998"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q4_1999"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q4_2000"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q4_2001"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q4_2002"                  0 KB       0 rows . . exported "IMSI"."SALES":"SALES_Q4_2003"                  0 KB       0 rows . . exported "IMSI"."TIMES"                                  0 KB       0 rows . . exported "IMSI2"."DEPT"                              6.031 KB       4 rows . . exported "IMSI2"."EMP"                               8.789 KB      14 rows . . exported "IMSI2"."EMPMIG"                            8.789 KB      14 rows Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:   /oraimsi/datapump/20251029_full.dmp Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Oct 29 22:38:39 2025 elapsed 0 00:03:06

 

 

마스터 테이블 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col segment_name for a20 select segment_name, bytes/1024/1024 mb from dba_segments where segment_name ='SYS_EXPORT_FULL_01';   SEGMENT_NAME                 MB -------------------- ---------- SYS_EXPORT_FULL_01            2

삭제되지 않고 존재함

 

 

테이블 컬럼 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

SQL> desc "SYSTEM"."SYS_EXPORT_FULL_01"  Name                      Null?    Type  ----------------------- --------- ---------------  ABORT_STEP                        NUMBER  ACCESS_METHOD                     VARCHAR2(16)  ANCESTOR_OBJECT_NAME              VARCHAR2(128)  ANCESTOR_OBJECT_SCHEMA            VARCHAR2(128)  ANCESTOR_OBJECT_TYPE              VARCHAR2(128)  ANCESTOR_PROCESS_ORDER            NUMBER  BASE_OBJECT_NAME                  VARCHAR2(128)  BASE_OBJECT_SCHEMA                VARCHAR2(128)  BASE_OBJECT_TYPE                  VARCHAR2(128)  BASE_PROCESS_ORDER                NUMBER  BLOCK_SIZE                        NUMBER  CLUSTER_OK                        NUMBER  COMPLETED_BYTES                   NUMBER  COMPLETED_ROWS                    NUMBER  COMPLETION_TIME                   DATE  CONTROL_QUEUE                     VARCHAR2(128)  CREATION_LEVEL                    NUMBER  CREATION_TIME                     DATE  CUMULATIVE_TIME                   NUMBER  DATA_BUFFER_SIZE                  NUMBER  DATA_IO                           NUMBER  DATAOBJ_NUM                       NUMBER  DB_VERSION                        VARCHAR2(60)  DEGREE                            NUMBER  DOMAIN_PROCESS_ORDER              NUMBER  DUMP_ALLOCATION                   NUMBER  DUMP_FILEID                       NUMBER  DUMP_LENGTH                       NUMBER  DUMP_ORIG_LENGTH                  NUMBER  DUMP_POSITION                     NUMBER  DUPLICATE                         NUMBER  ELAPSED_TIME                      NUMBER  ERROR_COUNT                       NUMBER  EXTEND_SIZE                       NUMBER  FILE_MAX_SIZE                     NUMBER  FILE_NAME                         VARCHAR2(4000)  FILE_TYPE                         NUMBER  FLAGS                             NUMBER  GRANTOR                           VARCHAR2(128)  GRANULES                          NUMBER  GUID                              RAW(16)  IN_PROGRESS                       CHAR(1)  INSTANCE                          VARCHAR2(60)  INSTANCE_ID                       NUMBER  IS_DEFAULT                        NUMBER  JOB_MODE                          VARCHAR2(21)  JOB_VERSION                       VARCHAR2(60)  LAST_FILE                         NUMBER  LAST_UPDATE                       DATE  LOAD_METHOD                       NUMBER  METADATA_BUFFER_SIZE              NUMBER  METADATA_IO                       NUMBER  NAME                              VARCHAR2(128)  OBJECT_INT_OID                    VARCHAR2(130)  OBJECT_LONG_NAME                  VARCHAR2(4000)  OBJECT_NAME                       VARCHAR2(200)  OBJECT_NUMBER                     NUMBER  OBJECT_PATH_SEQNO                 NUMBER  OBJECT_ROW                        NUMBER  OBJECT_SCHEMA                     VARCHAR2(128)  OBJECT_TABLESPACE                 VARCHAR2(128)  OBJECT_TYPE                       VARCHAR2(128)  OBJECT_TYPE_PATH                  VARCHAR2(200)  OLD_VALUE                         VARCHAR2(4000)  OPERATION                         VARCHAR2(8)  OPTION_TAG                        VARCHAR2(128)  ORIG_BASE_OBJECT_NAME             VARCHAR2(128)  ORIG_BASE_OBJECT_SCHEMA           VARCHAR2(128)  ORIGINAL_OBJECT_NAME              VARCHAR2(128)  ORIGINAL_OBJECT_SCHEMA            VARCHAR2(128)  PACKET_NUMBER                     NUMBER  PARALLELIZATION                   NUMBER  PARENT_OBJECT_NAME                VARCHAR2(128)  PARENT_OBJECT_SCHEMA              VARCHAR2(128)  PARENT_PROCESS_ORDER              NUMBER  PARTITION_NAME                    VARCHAR2(128)  PHASE                             NUMBER  PLATFORM                          VARCHAR2(101)  PROCESS_NAME                      VARCHAR2(128)  PROCESS_ORDER                     NUMBER  PROCESSING_STATE                  CHAR(1)  PROCESSING_STATUS                 CHAR(1)  PROPERTY                          NUMBER  PROXY_SCHEMA                      VARCHAR2(128)  PROXY_VIEW                        VARCHAR2(128)  QUEUE_TABNUM                      NUMBER  REMOTE_LINK                       VARCHAR2(128)  SCN                               NUMBER  SEED                              NUMBER  SERVICE_NAME                      VARCHAR2(64)  SIZE_ESTIMATE                     NUMBER  SRC_COMPAT                        VARCHAR2(60)  START_TIME                        DATE  STATE                             VARCHAR2(12)  STATUS_QUEUE                      VARCHAR2(128)  SUBPARTITION_NAME                 VARCHAR2(128)  TARGET_XML_CLOB                   CLOB  TDE_REWRAPPED_KEY                 RAW(2000)  TEMPLATE_TABLE                    VARCHAR2(128)  TIMEZONE                          VARCHAR2(64)  TOTAL_BYTES                       NUMBER  TRIGFLAG                          NUMBER  UNLOAD_METHOD                     NUMBER  USER_DIRECTORY                    VARCHAR2(4000)  USER_FILE_NAME                    VARCHAR2(4000)  USER_NAME                         VARCHAR2(128)  VALUE_N                           NUMBER  VALUE_T                           VARCHAR2(4000)  VERSION                           NUMBER  WORK_ITEM                         VARCHAR2(21)  XML_CLOB                          CLOB

많은 컬럼들이 존재함

참고로 데이터는 컬럼이 너무 많아 보기 힘들어 본문에는 넣지 않음

 

 

결론 :
오라클 19c의 datapump(expdp/impdp)에서는 datapump를 사용할때 생기는 마스터 테이블을 datapump 작업이 끝나고도 지워지지 않게하려면 keep_master 옵션을 사용하면됨

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump-export-utility.html#GUID-CAD6E280-4596-47F8-9236-96C422ED3334
https://mikedietrichde.com/2011/07/19/how-to-get-the-master-table-from-a-data-pump-expdp/