[Oracle] Oracle Data Pump 와 SQL*Loader

1. Oracle이 제공하는 Oracle Data Pump 기술은 하나의 데이터베이스에서 다른 데이터베이스로의 Data 및 Meta Data의 빠른 이동을 가능하게 한다. 여러분이 Oracle 8i나 9i에서 그 이상의 버전으로 시스템을 Upgrade할 때 기존 데이터베이스의 데이터를 Oracle이 제공하는 Data Pump 기술을 이용하여 이전하실 수 있다.

 

2. SQL*Loader는 외부 File의 데이터를 데이터베이스의 테이블에 넣기 위해 Oracle이 제공하는 Utility이다. 다양한 파일 형태로 저장되어 있는 문서들을 데이터베이스에 저장한다는 점에서 이 기능은 의미가 있다. 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------Oracle Data Pump

 

Data Pump Export

Data Pump Export나 Data Pump Import를 실행하기 위해 DBA나 CREATE ANY DIRECTORY 권한을 가진 사용자로부터 Directory Object를 먼저 생성해야 한다. 여러분이 Export나 Import를 수행하려면 DIRECTORY 파라미터에 Directory Object를 읽고 쓸 수 있도록 권한을 부여해야 한다.

 

아래 예에서 System 사용자로 접속하여 HR 사용자가 'dpump_dir'이라는 이름의 디렉터리를 읽고 쓸 수 있도록 권한을 부여하고 있다.

CONN system
CREATE DIRECTORY dpump_dir AS 'C:\dpump_dir';
GRANT READ, WRITE ON DIRECTORY dpump_dir TO HR;

Table Mode Export

HR 사용자 소유의 employees 테이블에 대해 Export를 수행해보자. 아래와 같이 명령어 프롬프트에서 수행한다.

C:\>EXPDP USERID=hr DIRECTORY=dpump_dir TABLES=employees

아래 화면은 EXPDP가 정상적으로 실행된 이후 C:\dpump_dir 경로에 생성된 export.log 파일의 내용이다.

;;;
Export: Release 10.2.0.1.0 - Production on 수요일, 04 4월, 2022 11:19:16

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
접속 대상: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
"HR"."SYS_EXPORT_TABLE_01" 시작 중: USERID=hr/******** DIRECTORY=dpump_dir TABLES=employees
BLOCKS 메소드를 사용하여 예측 진행 중...
객체 유형 TABLE_EXPORT/TABLE/TABLE_DATA 처리 중
BLOCKS 메소드를 사용한 총 예측: 64KB
객체 유형 TABLE_EXPORT/TABLE/TABLE 처리 중
객체 유형 TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT 처리 중
객체 유형 TABLE_EXPORT/TABLE/INDEX/INDEX 처리 중
객체 유형 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT 처리 중
객체 유형 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
객체 유형 TABLE_EXPORT/TABLE/COMMENT 처리 중
객체 유형 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT 처리 중
객체 유형 TABLE_EXPORT/TABLE/TRIGGER 처리 중
객체 유형 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 처리 중
. . "HR"."EMPLOYEES"                            15.83 KB     108행이 엑스포트됨
마스터 테이블 "HR"."SYS_EXPORT_TABLE_01"이(가) 성공적으로 로드됨/로드 취소됨
******************************************************************************
HR.SYS_EXPORT_TABLE_01에 대해 설정된 덤프 파일:
  C:\DPUMP_DIR\EXPDAT.DMP
"HR"."SYS_EXPORT_TABLE_01" 작업이 11.19.39에서 성공적으로 완료됨

정상적으로 수행이 되면, HR 소유의 사원 테이블(employees)에 대한 Data Pump Export의 결과를 EXPDAT.DMP라는 이름으로 확인할 수 있다.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

DIRECTORY 파라미터를 매번 설정학보다 아래와 같이 설정함으로써 설정을 생략할 수 있다.

 

내 컴퓨터 -> 속성 -> 고급 시스템 설정 -> 고급 -> 환경변수 ->  새로 만들기

변수 이름 :  DATA_PUMP_DIR

변수 값 : DPUMP_DIR

 

위와 같이 설정하면 DIRECTORY 파라미터를 설정하지 않아도 수행할 수 있다. 이때 저장되는 .dmp 파일의 경로는 Directory Object 생성 시 지정한 C:\dpump_dir 이다. 위와 같이 설정 하였다면 아래와 같이 Export를 수행해도 가능하다.

C:\> EXPDP USERID=scott/tiger TABLES=emp

데이터베이스 관리자로부터 DATA_PUMP_DIR Directory Object를 생성하지 않았다면 아래와 같은 메세지를 얻는다.

- ORA-39002: invalid operation

- ORA-39079: Unable to open the log file.

- ORA-39087: directory name DATA_PUMP_DIR is invalid

 

앞서 수행한 결과는 HR 소유의 사원 테이블의 전체 데이터에 대해 Data Pump Export를 수행한 것이다. 그럼 HR 사용자 소유의 다른 테이블에 대해 보다 다양한 데이터를 선택해 보고자 한다.

 

이번에는 HR 사용자 소유의 employees, departments, jobs 테이블에 대해 Export를 수행해 보자. 단 employees 테이블에 대해서는 급여가 5,000 이상인 경우에 대해서만 급여를 중심으로 내림차순 정렬 결과를 Export 한다. 아울러 Export할 때는 Data만을 가져온다. 즉, 각 테이블에 설정된 인덱스나 제약조건 등은 Export의 대상이 아니다.

 

위 경우 HR 소유의 테이블 중에서 몆 개의 테이블을 제외하고 나머지를 Export할 수 있어야 한다. 또한, 조건을 만족하는 특정 행만을 Export하는 방법을 제시해야 한다. 이 경우 Parameter File을 사용해서 위 조건을 만족할 수 있다. 아래와 같이 Parameter File 'hr_specific_data.ar'을 정의해 보자.

DIRECTORY=dpump_dir
DUMPFILE=dataonly.dmp
CONTEN=DATA_ONLY
INCLUDE=TABLE:"IN('EMPLOYEES','DEPARTMENTS','JOBS')"
QUERY=employees:"WHERE salary >= 5000 ORDER BY salary DESC"

 

CONTENT 옵션이란 무엇일까?

CONTENT라는 키워드를 통해 Export 할 떄 Data나 Metadata만을 선별적으로 가져올 수 있다.

- DATA_ONLY: 테이블의 데이터만을 가져온다.

- METADATA_ONLY: 데이터베이스 객체의 정의만을 가져온다.

- ALL: Data와 Metadata 모두를 가져오며 기본 옵션이다.