태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.


SQL*Loader 사용하기

오라클/SQL/SQL공부
 


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

 

 

2. SQL*Loader 특징
  - 하나 이상의 입력 파일을 사용 가능
  - 로드를 위해 여러 개의 입력 레코드가 하나의 논리적 레코드로 결합될 수 있습니다..
  - 입력 필드는 고정 길이 또는 다양한 길이를 가질 수 있습니다.
  - 입력 데이터는  등   어떤 유형도 가능 합니다.
  - 데이터는 디스크/테이프/명명된 파이프 등 여러 유형의 매체로부터 로드 가능  
  - 데이터가 한 번에 여러 테이블로 로드 가능
  - 텍스트 데이터는 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 로드의 경우 임시 세그먼트가 생성될 파일을 지정

 

 

 

 

 

 

 

 

 

 


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

 

 

 

  
   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 옵션을 사용하는 것은 서로 같다.

 
6. Data File
  ① 콘트롤 파일에서 정의된 형식의 입력을 위한 데이터가 있다.
  ② UpLoad될 데이터를 Control File에 정의 할 수도 있고 Data File에 따로 분리 할 수도 
      있다.
   
7. Log File
  - 로드 시작/종료 시간, 총로드시간등 정보를 제공 
  - Load상태 정보를 저장
  -로드된 행의 수, 에러로 인해 로드가 거부된 행의 수, 버려진(Discard) 행의 수등의
   정보를 제공 한다.
  

 

출처 : ONJ프로그래밍 (http://www.onjprogramming.co.kr)


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

 

 

 

 

(예제)

1. 연습용 테이블 생성

 


SQL> create table bbangmi_list(

  2  bbang_no           number not null primary key,

  3  food                       varchar2(30) not null,

  4  when                       varchar2(4)

  5  );

 


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

 

 

 

 

 

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

 

 

 

 

 

2. 제어파일 데이터가 들어있는 경우

(C:\Documents and Settings\onj에 저장했다)

 


myfood.ctl파일을 만들었다.


LOAD DATA

INFILE *

INTO TABLE bbangmi_list

FIELDS TERMINATED BY','

(BBANG_NO, FOOD, WHEN)

BEGINDATA

10, 떡볶이, 간식

20, 피자, 저녁

30, 치킨, 점심

40, 맥주, 아침

50, 커피, 아침

 

 

 

3. 실행

 

 

C:\Documents and Settings\onj>sqlldr userid=scott/tiger control = 'myfood.ctl'

 


SQL*Loader: Release 11.2.0.1.0 - Production on 금 8월 2 15:47:22 2013

 


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

 


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

 

 

 

 

 

 

4. 데이터 insert 실행여부 확인

(저장폴더에는 이제 myfood.log가 생성되어있다.)

 


SQL> connect scott/tiger

 

 

SQL> select*from bbangmi_list;

 


  BBANG_NO FOOD                           WHEN

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

        10 떡볶이                         간식

        20 피자                           저녁

        30 치킨                           점심

        40 맥주                           아침

        50 커피                           아침

 

 

 

 

 

 

 

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

 


2. 제어파일과 데이터 파일이 분리된 경우
#. control file 부분 : food.ctl저장


#. DATA : food.dat저장

 

 

@ food.ctl 내용


LOAD DATA

INFILE 'food.dat'       

APPEND                

INTO TABLE bbangmi_list

FIELDS TERMINATED BY ','

(bbangmi_no,food,when)

 

 

 

 

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

 

 

 

 

@ food.data 내용


60,금식,안돼

20,초콜렛,간식

10,콜라,저녁

 

 

 

(**주의!! data 파일에 빈칸이 있으면 자료가 안들어가 ㅠㅜㅠㅜㅠㅜㅠㅜ

예: 60, 금식, 안돼 nononono!!!

바른 답 : 60,금식,안돼 goooooooooood!!!

 

 

 

 


3. 실행


C:\Documents and Settings\onj>sqlldr userid=scott/tiger control = 'food.ctl'

 

 

SQL*Loader: Release 11.2.0.1.0 - Production on 금 8월 2 15:57:29 2013

 


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

 


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

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

 

 

 

 


(dir확인)


2013-08-02  오후 03:57                56 food.bad

2013-08-02  오후 03:55               110 food.ctl

2013-08-02  오후 03:56                54 food.DAT

2013-08-02  오후 03:57             1,972 food.log

2013-08-02  오후 12:08     1,455,095,808 full.dmp

2013-08-01  오전 11:20    <DIR>          My Documents

2013-08-02  오후 02:26               157 mydept.ctl

2013-08-02  오후 02:26             1,537 mydept.log

2013-06-22  오후 03:31       923,430,912 myemp.dmp

2013-08-02  오후 03:46               179 myfood.ctl

2013-08-02  오후 03:47             1,549 myfood.log

2013-06-15  오전 11:22    <DIR>          Oracle

2013-08-02  오전 11:34     1,455,095,808 scott.dmp

2013-06-22  오전 10:32             4,096 scott.emp

2013-07-18  오후 03:01                 0 select

2013-07-18  오후 03:02                 0 spool

2013-08-02  오전 11:44             8,192 structure.dmp

2013-07-18  오후 05:30                 0 test.sql

2013-08-02  오전 10:16    <DIR>          바탕 화면

2011-07-25  오후 03:41    <DIR>          시작 메뉴

              34개 파일       5,289,240,626 바이트

               7개 디렉터리  77,600,690,176 바이트 남음

 

 

 

food.bad 파일이 생성됨 :

----내용----

20,초콜렛,간식


10,콜라,저녁

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

 


(해석) BAD파일이 생긴 이유는 이미 기존에 위의 실습을 통해 10, 20, 30번,40번,50번은 저장 되어 있는 상태.

그러므로 APPEND 되는 10, 20, 60 번중에서 10, 20번을 가진 데이터는 DISCARD 되는 것

(bbang_no 가 Primary Key)

 

 

 

 


4. 데이터 insert 여부 확인


SQL> select * from bbangmi_list;

 


  BBANG_NO FOOD                           WHEN

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

        10 떡볶이                         간식

        20 피자                           저녁

        30 치킨                           점심

        40 맥주                           아침

        50 커피                           아침

        60 금식                           안돼

 


6 개의 행이 선택되었습니다.


 

블로그 이미지

구본숭 하늘을사랑한사람

진실된 사람으로 태어 나기 위한 조건들, 사랑하기 위해 필요한 것을 찾습니다.