오라클] ORACLE SQL*LOADER

2017.09.04 17:22


다른 시스템으로부터 중계서버를 거쳐 전달받은 데이터를 DB에 입력해야 하는데... 


우리 10년차 개발자님은 DB만 할 줄 알기 때문에 데이터를 DB로 넣는건 내가 해야 한다신다. 


뭐. 해야지. 


sql*loader 사용법을 정리해본다. 



sql*loader 사용법



sql*loader는 외부 데이터를 오라클에 입력하는 툴이다. 

(주로 대용량 데이터 입력에 사용)


$ORACLE_HOME/bin/sqlldr을 실행시키면 간략한 사용방법을 볼 수 있다

(오홋!. 이렇게 친절한거 좋다. ^^)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
[oracle@OEL73:/app/oracle/dbhome/bin]$ sqlldr
 
SQL*Loader: Release 11.2.0.1.0 - Production on 일 9월 3 11:01:05 2017
 
Copyright (c) 19822009, Oracle and/or its affiliates.  All rights reserved.
 
 
사용법: SQLLDR keyword=value [,키워드=값,...]
 
적절한 키워드:
 
    userid -- ORACLE 사용자 이름/비밀번호     
   control -- 제어 파일 이름입니다.
       log -- 로그 파일 이름입니다.
       bad -- 부적합한 파일 이름입니다.
      data -- 데이터 파일 이름
   discard -- 폐기 파일 이름
discardmax -- 허용할 폐기 수  (기본값 all)
      skip -- 건너 뛸 논리 레코드 수  (기본값 0)
      load -- 로드할 논리 레코드 수  (기본값 all)
    errors -- 허용할 오류 수  (기본값 50)
      rows -- 기본 경로 바인드 배열 또는 직접 경로 데이터 저장 사이의 행 수
               (기본값: 규약 경로 64, 직접 경로 전체)
  bindsize -- 기본 경로 바인드 배열 크기(바이트)  (기본값 256000)
    silent -- 실행 중 메시지 숨기기(헤더,피드백,오류,폐기,분할 영역)
    direct -- 직접 경로 사용  (기본값 FALSE)
   parfile -- 매개변수 파일: 매개변수 사양을 포함하는 파일 이름
  parallel -- 병렬 로드 수행  (기본값 FALSE)
      file -- 확장 영역을 할당할 파일
skip_unusable_indexes -- 사용할 수 없는 인덱스 또는 인덱스 분할 영역 허용 안함/허용  (기본값 FALSE)
skip_index_maintenance -- 인덱스 유지 관리 안함, 영향을 받은 인덱스를 사용 불가로 표시  (기본값 FALSE)
commit_discontinued -- 로드가 중단되는 경우 로드된 행 커밋  (기본값 FALSE)
  readsize -- 읽기 버퍼 크기  (기본값 1048576)
external_table -- 로드를 위해 외부 테이블 사용: NOT_USED, GENERATE_ONLY, EXECUTE  (기본값 NOT_USED)
columnarrayrows -- 직접 경로 열 배열에 대한 행 수  (기본값 5000)
streamsize -- 직접 경로 스트림 버퍼 크기(바이트)  (기본값 256000)
multithreading -- 직접 경로에서 다중 스레드 사용
 resumable -- 현재 세션에 대한 재개를 사용 또는 사용 안함으로 설정합니다.  (기본값 FALSE)
resumable_name -- 재개 가능한 명령문 식별에 도움이 되는 텍스트 문자열
resumable_timeout -- RESUMABLE에 대한 대기 시간(초)  (기본값 7200)
date_cache -- 날짜 변환 캐시 크기(항목)  (기본값 1000)
no_index_errors -- 인덱스 오류 발생 시 로드 중단  (기본값 FALSE)
 
주: 명령행 매개변수는 위치 혹은 키워드로 지정될 수 있습니다.
위치 또는 키워드별입니다. 전자의 예는 'sqlldr
scott/tiger foo'; 후자의 예는 'sqlldr control=foo
하나는 이전 위치에 의해 매개변수를 지정할 수 있으나 매개변수가
키워드에 의해 지정한 이후에는 할 수 없습니다.  예를 들어,
'sqlldr scott/tiger control=foo logfile=log'는 허용되지만
'sqlldr scott/tiger control=foo log'는
위치가 맞더라도 허용되지 않습니다
cs


대충 이해한 사용법은 sqlldr 명령어 뒤에 키워드=값, 키워드=값.... 형식으로 옵션을 사용한다.

(맨 밑 각주 내용을 보면 키워드를 생략하고 위치로 대신할 수 있는 모양인데 헷갈릴 수 있으니 키워드를 사용하는 방법을 쓰는걸로...)




sql*loader 구성 


sql*loader는 다음과 같은 구성으로 이루어진다. 


- 제어파일(control file) : 제일 중요. 어디에 저장된 데이터를 어떤 방식으로, 어떤 데이터 형식에 맞춰서 입력할지 결정


- 데이터 파일(data file) : DB에 입력할 데이터. 내용이 많다면 별도의 파일을 사용하고, 간단한 데이터라면 제어파일(control file) 안에 포함시키 수도 있다. 


- 로그 파일(log file) : 데이터 입력 과정에 대한 로그 기록


- 배드 파일(bad file) : 데이터 입력 과정 중에서 데이터 형식이나 조건 등의 문제 때문에 입력되지 않은 파일이 저장


- 폐기 파일(discard file) : 콘트롤 파일의 WHEN 절과 맞지 않는 데이터 저장

              (배드 파일은 insert실행시 오류가 난 데이터를, discard 파일은 insert구문도 실행할 수 없는 레코드를 남긴다)




제어 파일(control file)


SQL*LOADER에서 제일 중요하기도 하고, 여기서 필요한 것들을 전부 설정하기 때문에 조금 더 자세히 보자.



LOAD DATA   -- 콘트롤 파일 제일 앞 부분에 표시한다.

  UNRECOVERRABLE LOAD DATA 구문을 표시하면 리두 로그 정보 안남김

characterset -- 필요한 캐릭터셋 적용

INFILE * -- 입력해야 할 데이터가 컨트롤 파일에 포함될 경우 *를 붙인다. 

  데이터 파일 사용할 경우 INFILE '/DATA/INPUT.TXT' 

BADFILE '파일이름'

LOGFILE '파일이름'

DISCARDFILE '파일이름'

  -- 배드파일, 로그파일, 폐기파일 이름 저장

                   별도로 지정하지 않을 경우 CONTROL FILE과 같은 이름으로(확장자만 달리) 자동생성


INSERT/REPLACE/APPEND/TRUNCATE  -- 데이터 삽입 방법

INSERT -> 비어있는 테이블에 데이터 삽입

REPLACE -> 기존 행 삭제 후 삽입

APPEND -> 새로운 행을 기존의 데이터에 추가

TRUNCATE -> 테이블의 기존 데이터를 모두 삭제하고 삽입

INTO TABLE 테이블명 -- 데이터 입력할 테이블 명


WHEN -- 데이터 입력 전 만족시켜야 할 필드 조건 명시

  WHEN(10)='.' 이라면 10번째 필드가 소수점인 경우만 레코드 삽입


FILEDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- 데이터 필드 구분자 표시

(col1, col2, col3...) -- 컬럼명 표시


BEGINDATA -- 콘트롤 파일에 데이터를 같이 표시할경우 BEGINDATA 아래 데이터 표시




실습 예)


-- sql*loader를 이용해서 scott 사용자의 dept 테이블에 데이터 추가하기


-- 현재 dept 테이블 내용

1
2
3
4
5
6
7
8
9
10
 
SCOTT @ SQL > select * from dept;
 
    DEPTNO DNAME      LOC
---------- -------------- -------------
    10 ACCOUNTING      NEW YORK
    20 RESEARCH      DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS      BOSTON
 
cs



-- 컨트롤 파일 내용 (load_test.ctl)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[oracle@OEL73:~]$ cat load_test.ctl
 
LOAD DATA 
INFILE *    
APPEND
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
 
BEGINDATA
12,RESEARCH,"SARATOGA"     
10,"ACCOUNTING",CLEVELAND       
11,"ART",SALEM
13,FINANCE,"BOSTON"
21,"SALES",PHILA.
22,"SALES",ROCHESTER
42,"INT'L","SAN FRAN" 
 
cs



-- sql*loader 실행


1
2
3
4
5
6
7
8
[oracle@OEL73:~]$ sqlldr userid=scott/tiger control='load_test.ctl'
 
SQL*Loader: Release 11.2.0.1.0 - Production on 일 9월 3 15:38:23 2017
 
Copyright (c) 19822009, Oracle and/or its affiliates.  All rights reserved.
 
커밋 시점에 도달 - 논리 레코드 개수 7
[oracle@OEL73:~]$ 
cs



-- log 파일 조회


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
[oracle@OEL73:~]$ cat load_test.log
 
SQL*Loader: Release 11.2.0.1.0 - Production on 일 9월 3 15:38:23 2017
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
제어 파일:    load_test.ctl
데이터 파일:    load_test.ctl
  부적합한 파일:     load_test.bad
  폐기 파일:    지정 사항 없음
 
 (모든 폐기된 레코드 허용)
 
로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
바인드 배열:  64 행, 최대 256000 바이트
계속:    지정 사항 없음
사용된 경로:      규약
 
테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: APPEND
 
   열 이름                        위치    Len   Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,  O(") CHARACTER            
DNAME                                NEXT     *   ,  O(") CHARACTER            
LOC                                  NEXT     *   ,  O(") CHARACTER            
 
레코드 2: 기각됨 - 테이블 DEPT, 열 LOC에 오류
ORA-12899: "SCOTT"."DEPT"."LOC" 열에 대한 값이 너무 큼(실제: 16, 최대값: 13)
 
테이블 DEPT:
  6 행 로드되었습니다.
  데이터 오류 때문에 1 행(이)가 로드되지 않았습니다
  모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
  모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다
 
 
바인드 배열에 할당된 영역:             49536바이트(64 행)
읽기 버퍼 바이트: 1048576
 
생략된 논리 레코드의 합계:         0
읽어낸 논리 레코드의 합계:         7
거부된 논리 레코드의 합계:         1
폐기된 논리 레코드의 합계:         0
 
일 9월  03 15:38:23 2017에 실행 개시
일 9월  03 15:38:25 2017에 실행 종료
 
경과 시간:        00:00:02.15
CPU 시간:         00:00:00.03
cs



-- dept 테이블 조회


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SCOTT @ SQL > select * from dept;
 
    DEPTNO DNAME      LOC
---------- -------------- -------------
    12 RESEARCH      SARATOGA
    11 ART          SALEM
    13 FINANCE      BOSTON
    21 SALES      PHILA.
    22 SALES      ROCHESTER
    42 INTL      SAN FRAN
    10 ACCOUNTING      NEW YORK
    20 RESEARCH      DALLAS
    30 SALES      CHICAGO
    40 OPERATIONS      BOSTON
 
10 개의 행이 선택되었습니다.
cs


-- 아까 조회 때 4개. 실제 데이터 입력 6개

또루아빠 SQL(오라클,mysql) , , , , , , ,