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 개의 행이 선택되었습니다.
'프로그래밍 > Oracle' 카테고리의 다른 글
| 프로시저 혹은 함수 목록 출력시 (0) | 2014.06.25 |
|---|---|
| 오라클 날짜 함수 (0) | 2014.01.13 |
| ORA-01476 : 제수가 0 입니다. (0) | 2012.05.14 |
| ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다 (0) | 2012.04.27 |
| ORACLE PL/SQL 연습 날짜 출력하기 (0) | 2012.04.26 |