[Oracle 예제자료] SQL*Loader 사용법

[정민이]

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

1. SQL*Loader란 ?
외부 화일에 있는 데이터를 Oracle에 Upload 할 수 있는 유틸리티 입니다.
기본적으로 외부 데이타 화일, 콘트롤화일(로드하는 데이타의 정보를 저장)이 필요 
합니다.

2. SQL*Loader 특징
  - 하나 이상의 입력 파일을 사용 가능
  - 로드를 위해 여러 개의 입력 레코드가 하나의 논리적 레코드로 결합될 수 있습니다..
  - 입력 필드는 고정 길이 또는 다양한 길이를 가질 수 있습니다.
  - 입력 데이터는 문자, 이진, 팩 십진수,날짜, 존 십진수(zoned decimal)등  어떤 유형
    도 가능 합니다.
  - 데이터는 디스크/테이프/명명된 파이프 등 여러 유형의 매체로부터 로드 가능 
  - 데이터가 한 번에 여러 테이블로 로드 가능
  - 텍스트 데이터는 DB 버퍼 캐쉬를 거치지 않고 테이블로 직접 로드될 수 있다.
  - 테이블에 있는 기존 데이터를 대체 하거나(Replace) 추가(Append) 할 수 있는 기능
    이 있습니다.

3. SQL*Loader의 사용
  기본문법 :    SQLLDR [keyword=] value  [ [keyword=] value ]...
    - KEYWORD : 아래에 설명되는 키워드중의 하나이며 VALUE는  키워드에 지정되는 
      값 입니다. 
  실행예)SQLLDR userid=scott/tiger control='test.ctl' log='test.log' 

  ③ 키워드
  USERID :  사용자 ID와 암호
  CONTROL : 콘트롤 파일명
  LOG : 로그 파일명(기본 이름은 컨트롤파일명.log)
  BAD : 업로드에 실패한 레코드들이 저장 된다.(기본 이름은 컨트롤파일명.bad)
  DATA :  업로드를 위한 데이터 파일 이름 
  DISCARD : 선택되지 않은 레코드가 저장되는 디스카드 파일(선택 사항)
  DISCARDMAX :  버림(discard)의 최대 허용 갯수(기본값은 모든 버림을 허용하는 것입니다. 잘못된 입력 파일이 지정될 경우 실행을 중단시킬 안전 수단으로 사용) 
 SKIP : 건너 뛸 레코드 수, 주로 이전에 실패한 로드를 계속할 때 사용됨(한 테이블로 로드할 때나 로드되고 있는 모든 테이블에 대해 동일한 수의 레코드를 건너 뛸 때만 이 옵션을 사용)
 LOAD :  SKIP에 의해 지정된 레코드를 건너 뛴 다음 로드할 레코드 수를 지정 
 ERRORS : 배드 레코드의 최대 수
 ROWS : conventional 로드의 경우 각 삽입 전에 배열에 구축될 행의 수 지정(direct path로드의 경우 이 키워드는 각 data save마다 입력으로부터 읽어들일 행의 근사 수를정의합니다.Direct로드는 data save 전에 완전한 블록을 구축하고 버릴 행과 부적합한 행을거부한다.) 
 BINDSIZE : conventional 로드의 경우 각 데이터베이스 호출시 삽입될 행의 배열을 구축하는데 사용될 최대 바이트 수를 지정(ROWS 파라미터도 지정되면 SQL*Loader는 BINDSIZE의 제한에 따라 ROWS에 의해 정의된 만큼의 행을 구축합니다.) 
 DIRECT :  TRUE로 설정되면 SQL*Loader는 direct path를 사용. 반대의 경우는 기본 값인 conventional path 사용.
 PARFILE : 모든 로드 파라미터를 포함하고 있는 파일의 이름을 지정(명령 라인에정의된 파라미터는 파라미터 파일에 정의된 값들을 무효화합니다.) 
 PARALLEL : direct 로드에서만 적합한 이 파라미터는 다중 병렬 direct 로드가 수행되도록 지원 
 FILE : 병렬 direct 로드의 경우 임시 세그먼트가 생성될 파일을 지정

[참고]
- Conventional path 로드
Conventional path 로드는 삽입될 레코드의 배열을 구축하고 데이터를 로드하는데 INSERT 문을 사용하며 로드되는 동안 필드 지정에 따라 입력 레코드가 구문 분석되고 레코드의 배열이 구축되어 콘트롤 파일에 지정된 테이블로 삽입됩니다. 필드 지정을 따르지 않는 레코드는 거절(reject)되며 선택 조건을 충족시키지 않는 레코드는 버려집니다(Discard). INSERT를 이용하므로 내부적으로 데이터베이스 COMMIT를 사용합니다. 레코드 

- Direct path 로드
Direct path 로드는 메모리 내에 데이터의 블록을 구축하고 이 블록을 테이블에 할당된 익스텐트에 직접 저장합니다. 데이터베이스가 아카이빙 모드가 아니라면 리두 로그는 생성되지 않습니다. Direct path 로드는 일반적으로 conventional path 로드보다 빠르지만 모든 상황에서 사용할 수는 없습니다.

4. 실행방법
UNIX:  $sqlldr [keyword=]value [ [ [ , ] keyword=]value ] . . .
Windows : SQLLDR[80] [keyword=] value [ [ [ , ] keyword=] value] . . .

5. Control File
  Control File의 정의
  - Load할 데이터에 대한 설명을 기술한 파일
  - 데이터를 로드 할 오라클 테이블과 컬럼을 명시
  - 테이블 내에 데이터와 컬럼 사이의 종속 관계를 지정
   
  Control File 작성 Guide 
  - Control File은 대소문자 구분없이 작성 할 수 있습니다.
  - 대소문자 구분은 " "등으로 지정된 스트링 데이터를 제외하고는 중요하지 않습니다.
  - 주석은 -- 로 표시 합니다.
  - SQL*Loader의 예약어를 테이블 또는 컬럼명으로 사용 할 때에는 " "를 사용해야 합니다.
   
  Control File 예약어들
  - LOAD DATA : 제어화일의 시작을 명시 합니다.
  - INFILE : 외부화일을 지정 합니다. (ex. INFILE 'ulcase2.dat')
  - REPLACE : 테이블에 데이타 넣는 방법 지정 합니다.
  - INTO TABLE table_name :  데이타를 로드하는 테이블을 지정 합니다.
  - FIELD TERMINATED BY ',' : 데이타 필드의 종결문자를 지정 합니다.
 

  ※ Replace외에 다음과 같은 옵션이 있다.
    - REPLACE : 테이블의 기존 행을 모두 삭제(delete)하고 삽입
    - APPEND : 새로운 행을 기존의 데이타의 뒷부분에 추가
    - INSERT : 빈 테이블에 넣을 때 사용
    - TRUNCATE : 데이블에 이미 있는 기존 데이타를 truncate 하고 삽입

  사용방법
  - INFILE : 입력 데이터 파일의 이름
  - CONCATENATE와 CONTINUEIF : 사용한 입력 데이터 파일 내의 물리적 레코드로부터 만든 논리적 레코드 조합
  - FILEDS : 위치, 데이터 유형, 그리고 구분 문자 조건 지정을 포함하는 필드 지정
  - INTO TABLE :  사용하여 테이블 이름과 로드 방법, 즉 데이터가 빈 테이블로 로드될 것인지, 기존 레코드를 삭제하거나  잘라 버린 후 삽입될 것인지, 기존 데이터에 추가될 것인지 등 결정
  - CONTINUE_LOAD : 각 테이블에 대해 건너 뛸 레코드 지정
  - WHEN :  로드될 레코드를 선택하는데 사용될 조건 
  - 로드될 열 RECNUM과 SYSDATE 등의 절을 사용하거나 SQL 함수 적용등의 열값을 생성하는 규칙
  - 잘라 버리기(trimming), 0을 null로 대치하기 따위의 열 작업
  - OPTIONS 절을 사용하는 로드 파라미터
  - 병렬 direct 로드 동안 생성되는 임시 세그먼트에 대한 스토리지 지정
  - “--”로 시작하는 주석
  - direct 로드 옵션:
        - SINGLEROW: 행을 기초로 행에 대한 인덱스 유지
        - REENABLE: 로드가 끝날 때 disable되었던 제약 조건을 enable
        - SORTED_INDEXES: 데이터를 미리 정렬할 것인지 지정
        - UNRECOVERABLE: 리두 생성을 억제


 
  -----------------------  Control File 예(Control File에서 데이터를 포함 할 경우)     
    LOAD DATA
    INFILE *
    REPLACE
    INTO TABLE  mydept　　<-- 오라클의 테이블 이름
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    (DEPTNO, DNAME)
    BEGINDATA
    40,SALES
    50,"RESEARCH"
    60,"ART"
 
  참고 : 
  - Control File에서 데이터를 포함하지 않을 경우에는 INFILE 다음에 DATA파일을 지
        정하고 BEGINDATA 부터 생략 하면 된다.
  - NOLOGGING 키워드를 사용하여 테이블에 NOLOG 속성을 설정하는 것과 콘트롤 
    파일에 UNRECOVERABLE 옵션을 사용하는 것은 서로 같다.
  - 서로 다른 로드 옵션과 콘트롤 파일 항목들을 보여주는 여러 가지 사례 연구를 UNIX 
      의 경우 오라클8의 경우 $ORACLE_HOME/rdbms/demo 디렉토리에서 Windows 
      NT의 경우 %ORACLE_HOME%\RDBMS80\LOADER 디렉토리에서 찾아볼 수 있
      다. 
 
6. Data File
  ① 콘트롤 파일에서 정의된 형식의 입력을 위한 데이터가 있다.
  ② UpLoad될 데이터를 Control File에 정의 할 수도 있고 Data File에 따로 분리 할 수도 
      있다.
   
7. Log File
  - 로드 시작/종료 시간, 총로드시간등 정보를 제공 
  - Load상태 정보를 저장
  -로드된 행의 수, 에러로 인해 로드가 거부된 행의 수, 버려진(Discard) 행의 수등의
  정보를 제공 한다.
 

8. 예 

 연습용 테이블 생성
 
 SQL> create table mydept (
  2  deptno number not null primary key,
  3  dname varchar2(20) ,
  4  loc varchar2(20)
  5  );

테이블이 생성되었습니다.

SQL> create table myemp (
  2  empno number not null primary key,
  3  ename varchar2(20) not null,
  4  sal number(7,2),
  5  mgr number constraint fk_emp_empno references emp(empno)
  6  );

테이블이 생성되었습니다.


============================================================================
우선 제어파일에 데이터가 들어가 있는 경우를 해보겟습니다.
저의 경우 d:\sqlload 라는 디렉토리를 만들어 거기에 파일들을 만들었습니다.
 
-------------- mydept.ctl  -------------
LOAD DATA
INFILE *
INTO TABLE mydept <-- 오라클에 업로드될 테이블 이름
FIELDS TERMINATED BY ',' <-- 필드 구분자
(DEPTNO, DNAME, LOC)
BEGINDATA    -- 데이터 시작을 알림
10,관리부,서울
20,총무부,서울
30,영업부,서울
 
  - 위 파일을 d:\sqlload에 mydept.ctl 이라는 이름으로 저장하자.
  - 아래와 같이 실행하자. 
    D:\sqlload>sqlldr userid=scott/tiger control='mydept.ctl'

      SQL*Loader: Release 9.2.0.1.0 - Production on 금 Jan 9 00:43:06 2004

      Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

      커밋 시점에 도달 - 논리 레코드 개수 2
      커밋 시점에 도달 - 논리 레코드 개수 3

  - SQL*Plus를 통해 데이터가 INSERT 되었는지 확인 하자. 
  - 아래 d:\sqlload 디렉토리에 mydept.log 파일이 생겼을 것이다. 확인해보자.   
   
SQL> connect scott/tiger
연결되었습니다.
SQL> select * from mydept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 관리부              서울
        20 총무부              서울
        30 영업부              서울

===================================================================
다음의 경우는 제어파일과 데이터 파일이 분리된 경우입니다.

- Control File 부분은 dept.ctl로 저장 합니다.
- Data File 부분은 dept.dat로 저장을 합니다.
 
---------------------------------------- dept.ctl
LOAD DATA
INFILE 'dept.dat'      -- Data File을 지정 합니다. 
APPEND                  -- 기존에 데이터에 새로운 데이터를 추가 합니다.
INTO TABLE mydept
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC)
 
----------------------------------------
 

---------------------------------------- dept.dat
 
20,기획팀,서울
30,정보시스템부,부산
40,디자인팀,서울
 -------------------------------------------------

 
- SQL*Loader을 실행 시키자.
 
D:\sqlload>sqlldr userid=scott/tiger control='dept.ctl'

SQL*Loader: Release 9.2.0.1.0 - Production on 금 Jan 9 00:46:07 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 2
커밋 시점에 도달 - 논리 레코드 개수 3

D:\sqlload>dir
 D 드라이브의 볼륨: data1
 볼륨 일련 번호: 2434-2E50

 D:\sqlload 디렉터리

2004-01-09  12:46a      <DIR>          .
2004-01-09  12:46a      <DIR>          ..
2004-01-09  12:46a                  38 dept.bad  <-- BAD 파일이 생겼다. 확인하라
2004-01-09  12:45a                126 dept.ctl
2004-01-09  12:45a                  54 dept.dat
2004-01-09  12:46a              1,726 dept.log

              4개 파일          3,630 바이트
              2 디렉터리  2,187,476,992 바이트 남음
 

BAD파일이 생긴 이유는 이미 기존에 위의 실습을 통해 10, 20, 30번 부서는 저장 되어 있
는 상태이다. 그러므로 APPEND 되는 20, 30, 40 번중에서 20, 30번 부서코드를 가진 데이터는 DISCARD 되는 것이가.(deptno가 Primary Key 이므로...)

dept.bad 파일을 확인하면 다음과 같다.
-------------------------------
20,기획팀,서울
30,정보시스템부,부산
--------------------------------

scott/tiger로 접속을 하여 결과를 확인하자.
   
SQL> select * from mydept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        40 디자인팀            서울
        10 관리부              서울
        20 총무부              서울
        30 영업부              서울

이상에서는 mydept 테이블에서 연습을 하였다. 그럼 여러분들께서 이번에는 myemp 테이블을 이용하여 위의 두가지 경우를 실습해 보도록 하자.