[EXPDP/IMPDP]REMAP_DATA

expdp/impdp의 옵션 중 하나로 테이블 컬럼 값을 변경해줄 수 있게 해주는 옵션입니다.
패키지와 함수(function)가 사전에 정의되어 있어야합니다.

 

시나리오: SCOTT.EMP의 값을 여러번 insert 한 EMP_DUMPTEST라는 테이블을 가지고 SAL컬럼의 값을 EXPDP 또는 IMPDP 시 변경하는 테스트를 진행하겠습니다.

 

사용DB : Oracle 19.3.0.0(64bit)

사용OS : Linux 7.5(64bit)

 

1.테스트용 유저 테이블 생성
-유저는 C##TEST라는 유저를 사용하였습니다.(CDB모드)
sql>creste user C##TEST identified by test default tablespace USERS;
sql>grant connect,resource to C##TEST:

sql>conn C##TEST/test

sql>create table emp_dumptest as select * from scott.emp;

sql>create table emp_dumptest as select * from emp_dumptest;
/
/
/
/
/
/
/
/
(여러번 수행하였고, 한번만 해도 무방합니다. 대용량 테이블에 테스트하기 위해서 하였습니다.)
SQL> select count(*) from emp_dumptest;

  COUNT(*)
----------
     28672

데이터 확인
SQL> select distinct(sal) from emp_dumptest;

       SAL
----------
      1600
      2850
      1500
      2975
      5000
      2450
      1100
       950
      1300
       800
      3000

       SAL
----------
      1250

12 rows selected. 

 

현재 12개의 sal이 존재하며 remap_data를 통해 전부 0으로 만드는 작업을 할 것입니다.  

 

2.EXPDP/IMPDP 사용할 수 있는 디렉토리 생성 및 권한 주기
[ysbae19c:/home/oracle]> mkdir /home/oracle/test

SQL> create or replace directory test as '/home/oracle/test';

Directory created.

SQL> grant read,write on directory test to C##TEST;

Grant succeeded.

 

3.EXPDP (EMP_DUMPTEST만 expdp)

[ysbae19c:/home/oracle]> expdp system/oracle dumpfile=test:exp_emp2.dmp tables=C##TEST.EMP_DUMPTEST

Export: Release 19.0.0.0.0 - Production on Mon Feb 3 15:24:57 2020
Version 19.3.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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=test:exp_emp2.dmp tables=C##TEST.EMP_DUMPTEST
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "C##TEST"."EMP_DUMPTEST"                    1.140 MB   28672 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/oracle/test/exp_emp2.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Feb 3 15:25:21 2020 elapsed 0 00:00:22

 

4.패키지 생성

sql>CREATE OR REPLACE PACKAGE  remap_test AS
FUNCTION remapempsal (sal IN number ) RETURN number;
END remap_test;  
   /

Package created.

 sql>CREATE OR REPLACE PACKAGE BODY remap_test AS
     new_remapempsal number := 0;
 FUNCTION remapempsal (sal IN number) RETURN number IS
 BEGIN
   IF sal = 0 THEN
   return sal;
  else
   return new_remapempsal;
  end if;
 END remapempsal;
END remap_test;
/

 

5.IMPDP 실행

5-1 parfile 사용하기

parfile에 옵션을 넣고 parfile만 옵션으로 사용하는 방법입니다.
parfile 생성
[ysbae19c:/home/oracle/test]> cat emp_dumptest_imp.par
LOGFILE=emp_dumptest_impdp_200204.log
DUMPFILE=exp_emp2.dmp
TABLES=EMP_DUMPTEST
REMAP_DATA=EMP_DUMPTEST.SAL:REMAP_TEST.REMAPEMPSAL
TABLE_EXISTS_ACTION=TRUNCATE
DIRECTORY=TEST
[ysbae19c:/home/oracle/test]> impdp c##test/test parfile=emp_dumptest.par

Import: Release 19.0.0.0.0 - Production on Tue Feb 4 13:44:39 2020
Version 19.3.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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "C##TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "C##TEST"."SYS_IMPORT_TABLE_01":  c##test/******** parfile=emp_dumptest.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "C##TEST"."EMP_DUMPTEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "C##TEST"."EMP_DUMPTEST"                    1.140 MB   28672 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "C##TEST"."SYS_IMPORT_TABLE_01" successfully completed at Tue Feb 4 13:44:51 2020 elapsed 0 00:00:10


SQL> select distinct(sal) from emp_dumptest;

       SAL
----------
         0

 

5-2일반적으로 사용하기

[ysbae19c:/home/oracle/test]> impdp c##test/test dumpfile=test:exp_emp2.dmp tables=c##test.EMP_DUMPTEST REMAP_DATA=EMP_DUMPTEST.SAL:REMAP_TEST.REMAPEMPSAL

Import: Release 19.0.0.0.0 - Production on Tue Feb 4 14:35:01 2020
Version 19.3.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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "C##TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "C##TEST"."SYS_IMPORT_TABLE_01":  c##test/******** dumpfile=test:exp_emp2.dmp tables=c##test.EMP_DUMPTEST REMAP_DATA=EMP_DUMPTEST.SAL:REMAP_TEST.REMAPEMPSAL
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "C##TEST"."EMP_DUMPTEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "C##TEST"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Tue Feb 4 14:35:07 2020 elapsed 0 00:00:05

SQL> select distinct(sal) from emp_dumptest;

       SAL
----------
         0

 

- impdp시 REMAP_DATA옵션을 사용하여 sal의 값을 0으로 변경하였습니다.

반대로 EXPDP시 REMAP_DATA하는 테스트도 진행하겠습니다.

 

6.새로운패키지 생성

새로운 패키지는 0인 값을 10000으로 변경하는 패키지입니다.

SQL> CREATE OR REPLACE PACKAGE  remap_test2 AS
FUNCTION remapempsal (sal IN number ) RETURN number;
END remap_test2;
   /  

Package created.

SQL>  CREATE OR REPLACE PACKAGE BODY remap_test2 AS
     new_remapempsal number := 10000;
 FUNCTION remapempsal (sal IN number) RETURN number IS
 BEGIN
   IF sal = 0 THEN
   return new_remapempsal;
  else
   return 0;
  end if;
 END remapempsal;
END remap_test2;
/

 

7.EXPDP 실행

7-1일반적인 방법

[ysbae19c:/home/oracle/test]> expdp c##test/test dumpfile=test:emp_exp3.dmp tables=c##test.EMP_DUMPTEST remap_data=C##TEST.EMP_DUMPTEST.SAL:REMAP_TEST2.REMAPEMPSAL

Export: Release 19.0.0.0.0 - Production on Tue Feb 4 14:15:37 2020
Version 19.3.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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Starting "C##TEST"."SYS_EXPORT_TABLE_01":  c##test/******** dumpfile=test:emp_exp3.dmp tables=c##test.EMP_DUMPTEST remap_data=C##TEST.EMP_DUMPTEST.SAL:REMAP_TEST2.REMAPEMPSAL
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "C##TEST"."EMP_DUMPTEST"                    1.129 MB   28672 rows
Master table "C##TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for C##TEST.SYS_EXPORT_TABLE_01 is:
  /home/oracle/test/emp_exp3.dmp
Job "C##TEST"."SYS_EXPORT_TABLE_01" successfully completed at Tue Feb 4 14:16:12 2020 elapsed 0 00:00:33

 

5-2 parfile 사용하기

[ysbae19c:/home/oracle/test]> cat emp_dumptest_exp.par
LOGFILE=emp_dumptest_expdp_200204.log
DUMPFILE=exp_emp4.dmp
TABLES=EMP_DUMPTEST
REMAP_DATA=EMP_DUMPTEST.SAL:REMAP_TEST2.REMAPEMPSAL
DIRECTORY=TEST


C##을 인식못하여 에러가 발생할 수 있음
[ysbae19c:/home/oracle/test]> expdp c##test/test parfile=emp_dumptest_exp.par

Export: Release 19.0.0.0.0 - Production on Tue Feb 4 14:38:35 2020
Version 19.3.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
ORA-39001: invalid argument value
ORA-39146: schema "" does not exist

 

8.IMPDP 실행

ysbae19c:/home/oracle/test]> impdp c##test/test dumpfile=test:emp_exp3.dmp TABLE_EXISTS_ACTION=TRUNCATE

Import: Release 19.0.0.0.0 - Production on Tue Feb 4 14:18:15 2020
Version 19.3.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

Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

Master table "C##TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "C##TEST"."SYS_IMPORT_FULL_01":  c##test/******** dumpfile=test:emp_exp3.dmp TABLE_EXISTS_ACTION=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "C##TEST"."EMP_DUMPTEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "C##TEST"."EMP_DUMPTEST"                    1.129 MB   28672 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "C##TEST"."SYS_IMPORT_FULL_01" successfully completed at Tue Feb 4 14:18:26 2020 elapsed 0 00:00:09


9.확인
sal의 값이 0 -> 10000으로 변경된것을 확인하였습니다.
SQL> select distinct(sal) from emp_dumptest;

       SAL
----------
     10000

 

끝