IMPDP remap_table 옵션 사용하여 이관시 인덱스에 대한 테스트

설명 : IMPDP 옵션 중 테이블 이름을 변경하여 이관할 수 있는 remap_table 사용 시 인덱스는 어떻게 되는지에 대한 테스트를 진행하려 합니다. 상황은 직접 이름을 생성하여 만든 인덱스와 PK 제약조건 설정 시 자동으로 인덱스를 만들어주는 경우 두가지로 나눠 테스트를 진행하였습니다.

 

DB Oracle 12.2.0.1

 

1.직접 이름을 생성하여 만든 인덱스

직접 이름을 생성한 인덱스의 경우 이관 시 ORA-31684 에러 발생시키며 이관이 안됩니다.

--이관
impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=TEST_220105.dmp tables=TEST.BULKINS remap_table=BULKINS:BULKINS_RE logfile=BULKINS_RE.log

Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."BULKINS_RE"                         57.14 MB 4000001 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"TEST"."IX_BULKINS" already exists

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Wed Jan 5 16:37:01 2022 elapsed 0 00:00:08


--count 비교
SQL> select count(*) from BULKINS_RE
union all
select count(*) from BULKINS;

COUNT(*)         
---------------- 
         4000001
         4000001

2 rows selected.

--오브젝트 확인
SQL> select * From user_objects
where object_name='BULKINS_RE';

OBJECT_NAME                                                                                                                      SUBOBJECT_NAME                                                                                                                   OBJECT_ID          DATA_OBJECT_ID               OBJECT_TYPE             CREATED                    LAST_DDL_TIME              TIMESTAMP           STATUS       TEMPORARY          GENERATED          SECONDARY          NAMESPACE          EDITION_NAME                                                                                                                     SHARING            EDITIONABLE            ORACLE_MAINTAINED                  APPLICATION            DEFAULT_COLLATION                                                                                    DUPLICATED           SHARDED        CREATED_APPID              CREATED_VSNID              MODIFIED_APPID               MODIFIED_VSNID               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------------------------- ----------------------- -------------------------- -------------------------- ------------------- ------------ ------------------ ------------------ ------------------ ------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------------------- ---------------------------------- ---------------------- ---------------------------------------------------------------------------------------------------- -------------------- -------------- -------------------------- -------------------------- ---------------------------- ---------------------------- 
BULKINS_RE                                                                                                                                                                                                                                                                    103647                       103647 TABLE                   2022/01/05 16:36:56        2022/01/05 16:36:56        2022-01-05:16:36:56 VALID        N                  N                  N                                   1                                                                                                                                  NONE                                      N                                  N                      USING_NLS_COMP                                                                                       N                    N                                                                                                                             

1 rows selected.


--인덱스확인
SQL> select * from user_indexes
where table_name='BULKINS_RE';

INDEX_NAME                                                                                                                       INDEX_TYPE                  TABLE_OWNER                                                                                                                      TABLE_NAME                                                                                                                       TABLE_TYPE           UNIQUENESS           COMPRESSION            PREFIX_LENGTH              TABLESPACE_NAME                INI_TRANS          MAX_TRANS          INITIAL_EXTENT               NEXT_EXTENT            MIN_EXTENTS            MAX_EXTENTS            PCT_INCREASE             PCT_THRESHOLD              INCLUDE_COLUMN               FREELISTS          FREELIST_GROUPS                PCT_FREE         LOGGING        BLEVEL       LEAF_BLOCKS            DISTINCT_KEYS              AVG_LEAF_BLOCKS_PER_KEY                        AVG_DATA_BLOCKS_PER_KEY                        CLUSTERING_FACTOR                  STATUS       NUM_ROWS         SAMPLE_SIZE            LAST_ANALYZED              DEGREE                                   INSTANCES                                PARTITIONED            TEMPORARY          GENERATED          SECONDARY          BUFFER_POOL            FLASH_CACHE            CELL_FLASH_CACHE                 USER_STATS           DURATION         PCT_DIRECT_ACCESS                  ITYP_OWNER                                                                                                                       ITYP_NAME                                                                                                                        PARAMETERS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GLOBAL_STATS             DOMIDX_STATUS              DOMIDX_OPSTATUS                FUNCIDX_STATUS               JOIN_INDEX           IOT_REDUNDANT_PKEY_ELIM                        DROPPED        VISIBILITY           DOMIDX_MANAGEMENT                  SEGMENT_CREATED                ORPHANED_ENTRIES                 INDEXING         
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ---------------------- -------------------------- ------------------------------ ------------------ ------------------ ---------------------------- ---------------------- ---------------------- ---------------------- ------------------------ -------------------------- ---------------------------- ------------------ ------------------------------ ---------------- -------------- ------------ ---------------------- -------------------------- ---------------------------------------------- ---------------------------------------------- ---------------------------------- ------------ ---------------- ---------------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------------------- ------------------ ------------------ ------------------ ---------------------- ---------------------- -------------------------------- -------------------- ---------------- ---------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ -------------------------- ------------------------------ ---------------------------- -------------------- ---------------------------------------------- -------------- -------------------- ---------------------------------- ------------------------------ -------------------------------- ---------------- 

0 rows selected.

2.자동으로 설정되어 있는 인덱스의 경우

remap_Table을 사용하여 이관시 인덱스 생성됨

--이관
impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=TEST_220105.dmp tables=TEST.DEPT remap_table=DEPT:TEST2 logfile=TEST_RE.log


Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=TEST_220105.dmp tables=TEST.DEPT remap_table=TEST.DEPT:TEST.DEPT_RE logfile=DEPT_RE.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."TEST"                               6.023 KB       4 rows
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/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 5 16:33:39 2022 elapsed 0 00:00:24


--remap_table된 이름 확인
SQL> select * From user_objects
where object_name='TEST2';

OBJECT_NAME                                                                                                                      SUBOBJECT_NAME                                                                                                                   OBJECT_ID          DATA_OBJECT_ID               OBJECT_TYPE             CREATED                    LAST_DDL_TIME              TIMESTAMP           STATUS       TEMPORARY          GENERATED          SECONDARY          NAMESPACE          EDITION_NAME                                                                                                                     SHARING            EDITIONABLE            ORACLE_MAINTAINED                  APPLICATION            DEFAULT_COLLATION                                                                                    DUPLICATED           SHARDED        CREATED_APPID              CREATED_VSNID              MODIFIED_APPID               MODIFIED_VSNID               
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------------------------- ----------------------- -------------------------- -------------------------- ------------------- ------------ ------------------ ------------------ ------------------ ------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------------------- ---------------------------------- ---------------------- ---------------------------------------------------------------------------------------------------- -------------------- -------------- -------------------------- -------------------------- ---------------------------- ---------------------------- 
TEST2                                                                                                                                                                                                                                                                         103674                       103674 TABLE                   2022/01/05 16:40:13        2022/01/05 16:40:14        2022-01-05:16:40:13 VALID        N                  N                  N                                   1                                                                                                                                  NONE                                      N                                  N                      USING_NLS_COMP                                                                                       N                    N                                                                                                                             

1 rows selected.

--인덱스 확인
SQL> select * from user_indexes
where table_name='TEST2';

INDEX_NAME                                                                                                                       INDEX_TYPE                  TABLE_OWNER                                                                                                                      TABLE_NAME                                                                                                                       TABLE_TYPE           UNIQUENESS           COMPRESSION            PREFIX_LENGTH              TABLESPACE_NAME                INI_TRANS          MAX_TRANS          INITIAL_EXTENT               NEXT_EXTENT            MIN_EXTENTS            MAX_EXTENTS            PCT_INCREASE             PCT_THRESHOLD              INCLUDE_COLUMN               FREELISTS          FREELIST_GROUPS                PCT_FREE         LOGGING        BLEVEL       LEAF_BLOCKS            DISTINCT_KEYS              AVG_LEAF_BLOCKS_PER_KEY                        AVG_DATA_BLOCKS_PER_KEY                        CLUSTERING_FACTOR                  STATUS       NUM_ROWS         SAMPLE_SIZE            LAST_ANALYZED              DEGREE                                   INSTANCES                                PARTITIONED            TEMPORARY          GENERATED          SECONDARY          BUFFER_POOL            FLASH_CACHE            CELL_FLASH_CACHE                 USER_STATS           DURATION         PCT_DIRECT_ACCESS                  ITYP_OWNER                                                                                                                       ITYP_NAME                                                                                                                        PARAMETERS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               GLOBAL_STATS             DOMIDX_STATUS              DOMIDX_OPSTATUS                FUNCIDX_STATUS               JOIN_INDEX           IOT_REDUNDANT_PKEY_ELIM                        DROPPED        VISIBILITY           DOMIDX_MANAGEMENT                  SEGMENT_CREATED                ORPHANED_ENTRIES                 INDEXING         
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ---------------------- -------------------------- ------------------------------ ------------------ ------------------ ---------------------------- ---------------------- ---------------------- ---------------------- ------------------------ -------------------------- ---------------------------- ------------------ ------------------------------ ---------------- -------------- ------------ ---------------------- -------------------------- ---------------------------------------------- ---------------------------------------------- ---------------------------------- ------------ ---------------- ---------------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------------------- ------------------ ------------------ ------------------ ---------------------- ---------------------- -------------------------------- -------------------- ---------------- ---------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ -------------------------- ------------------------------ ---------------------------- -------------------- ---------------------------------------------- -------------- -------------------- ---------------------------------- ------------------------------ -------------------------------- ---------------- 
SYS_C009846                                                                                                                      NORMAL                      TEST                                                                                                                             TEST2                                                                                                                            TABLE                UNIQUE               DISABLED                                          USERS                                           2                255                        65536                1048576                      1                2.1e+09                                                                                                                                                  10 YES                       0                      1                          4                                              1                                              1                                  1 VALID                       4                      4 2021/12/31 10:29:55        1                                        1                                        NO                     N                  Y                  N                  DEFAULT                DEFAULT                DEFAULT                          NO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  YES                                                                                                             NO                   NO                                             NO             VISIBLE                                                 YES                            NO                               FULL            

1 rows selected.