백업&복구 12번째(SQL loader)

시작하기 전에!
vi로 각각의 줄마다 마지막에 문자추가하기(@를 추가한다고 합시다)
:%s/$/@/g
 
High Water Mark(HWM) ; 직역시, 최대수위표시 
: oracle에서는 datafile에 얼만큼 사용했는지 말하는 용어이다.
 

SQL Loader : oracle이외에서 작성된 파일을, DB로 옮기는 기능
( http://www.goodus.com/knowledge_pds/%EA%B8%B0%EC%88%A0%EB%85%B8%ED%8A%B8[43%ED%9A%8C]_SQLloader.pdf  
; SQL Loader문서) 


SQL Loader은 2가지 방법을 사용한다.
1. Conventional Path(기본모드)
    HWM 왼쪽에서 free block을 찾은 후, DB cache에 올려 자료를 삽입하다가 더이상 free block이 없으면 HWM Bump up을 일으킨 후, 빈 block을 DB Cache에 올려에 자료를 삽입
장점 : 작업실패시 복구가능
단점 : DB Cache, Redo, Undo, datafile 모두 사용하기 때문에 속도가 느리다.

2. Direct Path
    무조건, HWM우측에 data를 잡아넣는다. 
장점 : datafile에 직접 넣어버리기 때문에 속도가 빠릅니다.
단점 : 실패시 포기하고 처음부터 다시해야합니다., 중간에 다른 작업이 들어와서 HWM가 바껴버리면 안되기 때문에, SQL Loader중 Direct path mode로 작업시에는 해당 datafile을 SQL LOADER가 독점합니다.

----> direct path는 insert작업시에 사용합니다.(중간에 에러나도, 지우고 다시 하면 되니까)

SQL Loader을 사용하기 위해서는 controlfile과 자료파일이 있어야합니다.
control file은 vi로 직접 만들어주면 됩니다.

** control file sample
LOAD DATA
INFILE '/home/oracle/example.csv'
DISCARDFILE '/home/oracle/exam_discard.dsc'
     ## where절의 조건에 해당하지않아 의도적으로 폐기되는 파일
REPLACE
    ## 테이블이 존재하면, 내용을 다 지우고 입력하라는 의미(APPEND : 추가)
INTO TABLE example
WHEN
    ## 입력하고자 하는 조건
FIELDS TERMINATED BY ','
     ## 각 필드의 구분기호(때에따라 \를 붙여줘야합니다, 예: ~가 구분기호일때에는 '\~')
OPTIONALLY ENCLOSED BY '"'
    ## 추가적으로 필드가 "adf" 형식으로 쌓여져 있을때 구분기호
TRAILING NULLCOLS
    ## 컬럼에 null값이 들어가는 것들은 null을 입력해줌(입력안하면 자료가 없을때 에러가 납니다)
(id, name, addr "nvl(:addr,'서울')" )
    ## 테이블의 컬럼이름, addr 뒤에 "nvl(:addr,0)" 부분은 만약 addr이 null값이면 '서울'을 입력하라는 말입니다.

직접 사용해보겠습니다.

   

SampleData.csv

위 데이터를 DB에 입력하겠습니다.

1. 위에 맞게 테이블 생성

SQL> create table sample
  2  (region varchar(15),
  3  rep varchar(15),
  4  item varchar(15),
  5  units number,
  6  unit_cost number,
  7  total number);

Table created.

2. SQL Loader의 control file생성
vi /home/oracle/sample.ctl

[oracle@server15 ~]$ cat sample.ctl

load data

infile 'SampleData.csv'

replace

into table sample

fields terminated by ','

optionally enclodes by '"'

trailing nullcols

(region, rep, item, units, unit_cost, total)

3. SQL Loader로 DB에 입력

[oracle@server15 ~]$ sqlldr koo/mong_1 control=sample.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Sun Feb 19 15:10:27 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 43   << 이후 반드시 log파일이나 bad파일을 확인하세요

4.  log파일 확인(cat sample.log)

아래쪽에

Total logical records skipped:          0

Total logical records read:            43

Total logical records rejected:         0

Total logical records discarded:        0

; 43개의 레코드가 읽혀졌고, 거부되거나 버려지거나 건너뛰는것 없이 삽입되었다는 의미입니다. 

5. DB에서 확인

 select * from koo.sample;

REGION          REP             ITEM                 UNITS  UNIT_COST      TOTAL
--------------- --------------- --------------- ---------- ---------- ----------
Quebec          Jones           Pencil                  95       1.99     189.05
Ontario         Kivell          Binder                  50      19.99      999.5
Ontario         Jardine         Pencil                  36       4.99     179.64
Ontario         Gill            Pen                     27      19.99     539.73
....생략....
Ontario         Jardine         Binder                  11       4.99      54.89
Ontario         Jardine         Binder                  94      19.99    1879.06
Ontario         Andrews         Binder                  28       4.99     139.72

43 rows selected.

** SQL Loader사용시 주의사항
* 만약, 값중에 입력이 안된 값(null)이 있고, 그것들을 sqlldr을 이용해 넣을 때, 줄의 마지막(레코드마지막)에 
fields terminated by ',' 일 경우에는  , 문자를 추가하시고 sqlldr을 이용해 넣으면 깔끔하게 잘 들어갑니다.

각 줄의 마지막마다 , 문자추가하기
vi에서 :%s/$/,/g 

 * fields terminated by ',' 일 경우에 넣으려는 데이터파일 중 ,, 가 있으면 해당 column값은 null인 것입니다.

* 엑셀로 작업했다면, 마지막에 저장할때 서식을 일반으로 바꾸고 저장하시는것이 편합니다.(숫자중에 1,425 이런 경우가 있어서)


** Additional Example

add.txt

위 파일은 아래같은 자료들이 들어가 있습니다.

34455,윤진욱,ansel414,1,8405162123648,055)261-8947,168,52,101,9906

34456,이동훈,mandu,3,8103211063421,051)824-9637,170,88,102,9907

34457,박동진,Ping2,1,8511241639826,051)742-6384,182,70,102,9907

34458,김진경,simply,2,8206062186327,055)419-6328,164,48,102,

34459,조명훈,Gomo00,2,8004122298371,055)418-9627,161,42,102,9903

34460,전인하,yousuk,4,7709121128379,051)724-9618,177,92,201,9907

넣을 테이블의 구조

SQL> desc student
 Name                                                        Null?    Type
 ----------------------------------------------------------- -------- ---------------------

 STUDNO                                                      NOT NULL NUMBER(5)
 NAME                                                                 VARCHAR2(10)
 USERID                                                               VARCHAR2(10)
 GRADE                                                                VARCHAR2(1)
 IDNUM                                                                VARCHAR2(13)
 BIRTHDATE                                                            DATE
 TEL                                                                  VARCHAR2(13)
 HEIGHT                                                               NUMBER(5,2)
 WEIGHT                                                               NUMBER(5,2)
 DEPTNO                                                               NUMBER(4)
 PROFNO                                                               NUMBER(4)
 

  BIRTHDATE의 경우는 default값이 sysdate

위 자료를 넣을 컨트롤파일의 내용

1. 구분연산자를 사용한 자료의 경우
LOAD DATA

INFILE 'add.txt'

APPEND

INTO TABLE student

FIELDS TERMINATED BY ','

(studno, name, userid, grade, idnum, tel, height, weight, deptno, profno)

2. 위치를 이용해서 넣을때

load data

infile 'test.prn'

replace

into table test_ldr

trailing nullcols

(

empno position(1:4) integer external,

name  position(6:9) char,

pos   position(11:12) char,

sal   position(14:17) integer external,

incent position(18:19) integer external "nvl(:incent,1000)",

deptno position(21:22) integer external)