[데이터 이동하기] SQL*Loader

[오라클에서 데이터를 입력하는 방식]

1. Conventional Path 방식

- 데이터가 입력되거나 조회될때 항상 메모리(database buffer cache)를 통해서 작업되는 방식

- 장점 : 데이터를 load하는 도중 다른 사용자와 작업승인 테이블을 공유해서 작업할수 있음

- 단점 : direct load 방식에 비해 속도가 느리고 redo log와 undo log가 많이 발생

- 자주 사용하는 블록을 메모리에 공유해서 디스크 I/O를 줄이기 위해 도입된 방식

- but, 재사용 가능성이 없는 블록을 대량으로 쓸경우에는 오히려 성능저하

- 공간이 부족할때 *HWM Bump up을 하면서 소요되는 시간때문에 대량의 데이터를 입력할때 문제가 있음.

2. Direct Path 방식 (Bulk Load방식)

- data를 입출력할때 메모리(database buffer cache)를 거치지 않고 바로 블록에 작업을 하는 방식

- 데이터를 입력할때 HWM 왼쪽블록부터 사용하는것이 아니라 HWM 오른쪽 블록(비어있는 블록)을 사용 

- HWM 오른쪽 블록(비어있는 블록)을 사용하여 데이터를 한꺼번에 입력하고 commit하는 방식 -> HWM Bump up 필요없음

- HWM 오른쪽에 입력되는 블록이라 다른 세션과 공유도 되지 않음 -> undo 데이터도 필요없음

- rollback을 할 경우에도 실제 블록은 그대로 두고 딕셔너리 정보만 롤백 -> rollback시간이 적게 소요됨

- but, redo를 사용하지 않기때문에 중간에 장애가 발생할 경우 처음부터 다시 입력해야 함

- 장점 : 속도가 빠르다

- 단점 : 해당 입력작업을 할때 해당 테이블에 Exclusive Lock을 설정하기 때문에 

 다른 트랜잭션이 DML을 일으키지 못하도록 막게 설정되어있어 동시에 변경작업 불가

- 사용량이 적은 시간에 사용해야 하며, 이 기능을 사용하려면 해당 테이블에서 nologging옵션이 활성화 되어 있어야함

- 오라클에서는 대량의 데이터를 입,출력할때 direct path방법을 지원

① 정렬할때 (Temp segment에 읽고 쓰기)

② 병렬 작업 할때 ( Parallel, Append 옵션을 사용할때)

③ Direct 옵션 사용시 (export, datapump 등)

* HWM (High Water Mark)

- 데이터 파일에서 사용한 블록을 표시하는 마크, 일종의 책갈피

- Table Full Scan을 하게 되면 해당 데이터파일에서 첫번째 블록부터 HWM까지 블록을 읽음

- 데이터 입력시 HWM 왼쪽블록을 다 사용하게되면 입력작업을 잠시 중지하고 HWM 오른쪽으로 이동하여 빈 블록(Extent) 확보 후 데이터 입력

- HWM를 오른쪽으로 이동하여 빈 블록을 확보하는 과정을 HWM Bump up이라고 함

---

[SQL*Loader]

- Direct Path 방식을 이용하여 대량의 데이터를 아주 빠른속도로 입력할수 있는 유틸리티

- 잘못사용하게되면 심각한 문제발생할수 있으므로 주의해서 사용해야함

- $ORACLE_HOME/rdbms/admin/catldr.sql 파일이 수행되면서 설치됨

- 위 스크립트 파일은 catalog.sql 스크립트가 실행되면서 자동으로 호출이 되어 실행됨

- 다른 응용프로그램 (ex. 엑셀)에 저장되어있던 대량의 데이터를 오라클로 입력할때 사용하는 유틸리티

- 오라클 설치시 기본적으로 설치됨

1. SQL*Loader 수행 전 미리 확인해야 할 사항

2. SQL*Loader의 구조

3. SQL*Loader를 구성하는 주요 파일들

4. SQL*Loader 실행 및 옵션확인

[oracle@localhost ~]$ sqlldr

SQL*Loader: Release 11.2.0.2.0 - Production on Wed Aug 13 03:00:24 2014

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

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

    userid -- ORACLE username/password

   control -- control file name

       log -- log file name

       bad -- bad file name

      data -- data file name

   discard -- discard file name

discardmax -- number of discards to allow          (Default all)

      skip -- number of logical records to skip    (Default 0)

      load -- number of logical records to load    (Default all)

    errors -- number of errors to allow            (Default 50) 

 --에러옵션을 충분히 준 후 작업하는것이좋음 (에러를 몇개까지 기록할 것인가, 설정값보다 에러가 많이 발생할 경우 SQL*Loader 작업이 중단됨)

      rows -- number of rows in conventional path bind array or between direct path data saves

               (Default: Conventional path 64, Direct path all)

  bindsize -- size of conventional path bind array in bytes  (Default 256000)

    silent -- suppress messages during run (header,feedback,errors,discards,partitions)

    direct -- use direct path                      (Default FALSE)

   parfile -- parameter file: name of file that contains parameter specifications

  parallel -- do parallel load                     (Default FALSE)

      file -- file to allocate extents from

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)

commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)

  readsize -- size of read buffer                  (Default 1048576)

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)

columnarrayrows -- number of rows for direct path column array  (Default 5000)

streamsize -- size of direct path stream buffer in bytes  (Default 256000)

multithreading -- use multithreading in direct path

 resumable -- enable or disable resumable for current session  (Default FALSE)

resumable_name -- text string to help identify resumable statement

resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)

date_cache -- size (in entries) of date conversion cache  (Default 1000)

no_index_errors -- abort load on any index errors  (Default FALSE)

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords.  An example of the former case is 'sqlldr

scott/tiger foo'; an example of the latter is 'sqlldr control=foo

userid=scott/tiger'.  One may specify parameters by position before

but not after parameters specified by keywords.  For example,

'sqlldr scott/tiger control=foo logfile=log' is allowed, but

'sqlldr scott/tiger control=foo log' is not, even though the

position of the parameter 'log' is correct.

---

[실습]

1. 입력할 데이터가 외부 프로그램 자료일 경우

# 외부프로그램(엑셀)에 입력된 자료 준비

# 엑셀파일을 저장하되 .CSV 확장자로 저장 (파일이름 : ani.csv)

# 입력할 외부데이터를 WinSCP를 사용하여 오라클서버로 이동

# 복사한 파일 확인

# 입력할 외부데이터를 오라클 서버에 가져온후 (.csv파일) vi로 열어서 필요한 데이터부분만 남기고 편집

# 데이터를 입력할 테이블 생성 (테이블명 : f_animal)

# SQL*Loader용 Control File생성

 load data

   infile "/home/oracle/ani.csv"  --외부파일위치

   into table f_animal  --데이터를 입력할  테이블 지정

   fields terminated by ','  --컬럼구분자

   (no, name, loc)  --컬럼명

# SQL*Loader 실행

$ sqlldr scott/tiger control='/home/oracle/f_ani.ctl' --control file을 지정하여 SQL*Loader실행

# 데이터를 입력한 테이블 조회하여 입력된 데이터 확인

 # 따로 설정하지 않아도 log file이 생성됨

파일명은 SQL*Loader용 conrol file명과 같고, 확장자는 .log

2. 입력 데이터가 control file 내부에 있을 경우

3. 각종 파일 사용하기 (BAD File, DISCARD File 사용하기)

4. control file에서 position 사용하기

5. Null 값을 0으로 표시하기

6. 원격지 서버에 입력하기