백업&복구 20 - SQL Loader

- 모든 DML의 원리
  : ①②③ 파일에서 블락찾고 메모리에 올려서 처리하고 내려씀
  
 

※ 테이블에 데이터를 넣을때
 
1. Conventional Path - 느리지만, 고칠수있다.
  - 메모리를 거쳐서 작업하기 때문에 작업중 에러나면 고칠수 있다.
  - HWM 기준으로 왼쪽으로 검색해보고 공간이 부족하면 HWM를 오른쪽으로 이동시켜 공간확보 후 다시 입력시도
    : HWM Bumpup ← 위과정의 이름
 
 
※ 참고
HWM (High Water Mark)란?
 : 데이터 파일에서 사용한 블록을 표시하는 마크로서 일종의 책갈피와 같은 역할
   Table Full Scan 을하게 되면 해당 데이터파일에서 첫 번째 블록부터 HWM까지 읽게 된다.
 : 해당 데이터파일에서 어디까지 사용했다는 의미로 표시해 두는것
 
 
2. Direct Path (=Bulk Load) - 빠름
  - 미사용부분에 바로 데이터 붓는다.
  - Data를 입출력할때 DB cache를 거치지 않고 바로 블록에 작업
  - 안고쳐도 되는 작업에 쓴다 = Insert , select
  - 단점 : 복구가 안됨
         : 혼자 사용할때 씀(자원거의다 써서 다른작업이 힘들다.)
Ex) 마이그레이션 할때 많이 씀
 
 
 
 

 
 
※ SQL Loader
다른 플랫폼자료를 오라클에 입력해야 하는 경우 사용
Direct Path 방식으로 데이터를 붓는다.
Ex) 엑셀자료(10년치) → SQL Loader → 오라클
 
Cf) 
-exp/imp
-expdp/impdp
특징 : 오라클 자료 → 오라클로 


 

※ 거의 대부분의 에러 : 캐릭터셋이 안맞아서 일어난다.
 
★ SQL Loader 사용전 확인 사항
1. NLS_LANG 환경변수 체크
$ env | grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.KO16MSWIN949
 
2. ORACLE_HOME 환경변수 체크
$ env | grep ORACLE_HOME
ORACLE_HOME=/home/oracle/product/10g
 
→ 이 변수가 잘못되면 ORA-12560: TNS:protocol adapter error 가 발생 할수 있다.
 
3. LD_LIBRARY_PATH 환경변수 체크 (Unix 만 해당)
$ env | grep LD_LIBRARY_PATH
LD_LIBRARY_PATH=/home/oracle/product/10g/lib:/lib:/usr/lib:/usr/local/lib
 
→ 이 변수가 잘못되면 libwtc8 library cannot be found. 에러가 발생 할수 있다.
 
4. ORA_NLS33 이나 ORA_NLS10 환경변수 확인
$ env | grep ORA_NLS33
$ env | grep ORA_NLS10
ORA_NLS10=/home/oracle/product/10g/nls/data
 
※ 참고 : ORA_NLSxx 환경변수 버전별 적용 및 수정
$ vi /home/oracle/.bash_profile
 
- 오라클 7 버전일 경우
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data
 
- 오라클 8 ,8i ,9 버전일 경우
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
 
- 오라클 10g 버전일 경우
ORA_NLS10=$ORACLE_HOME/nls/data
 
 
오라클 9i 버전부터는 위 파라미터를 지정하지 않아도 기본 디렉토리를 사용한다.
ORA_NLS32 파라미터는 8 버전 이상부터는 사용할 필요 없으며, ORA_NLS33 파라미터는 10g 버전 이상부터 사용할 필요가 없다.
 
★ 본인 환경은 10g이므로
export ORA_NLS10=$ORACLE_HOME/nls/data
export LANG=C
이것 두개 추가

 
 



 
※ SQL Loader 구성
 

▶ Control file
- 오라클 운영용 control file 이 아니라 SQL Loader를 사용하기 위한 정보를 가진 control file 이다.(별개임)
- 확장자는 ctl이며 SQL Loader를 실행시키기 위한 각종 정보가 들어 있다.


LOAD DATA
: 새로운 데이터 입력이 시작됨을 알림
  작업 중 중단되어 다시 시작할 경우 CONTINUE LOAD DATA 문장을 사용
 
INFILE *
: 입력하고자 하는 데이터파일이 외부에 있을 경우 * 대신 파일이름적어줌
  컨트롤 파일 내부에 있을 경우 * 을 사용
 
BADFILE ‘bfile.bad’
: 입력 거부된 목록을 저장할 파일 - 문법상에러
  입력 형태가 틀리거나 제약조건 위배시 이 파일에 기록됨. 내용 수정 후 다시 입력 가능
 
DISCARDFILE ‘discard.dsc’
: 폐기된 파일 목록을 저장할 파일 - 조건상에러
  Where 절의 조건에 해당하지 않아 폐기되는 파일을 기록. 내용 수정 후 다시 사용 가능
 
REPLACE  : 테이블에 기존 행을 모두 삭제(delete)한 후 다시 입력하라는 의미
APPEND   : 기존 내용 아래에 신규 내용 추가
INSERT   : 내용 없는 테이블에 신규 데이터를 입력
TRUNCATE : 기존내용 모두 truncate 한후 다시 입력
 
INTO TABLE stest
: 데이터를 입력할 테이블명을 적어준다.
 
WHEN
: 입력하고자 하는 조건이 있을 경우 작성
 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
: 각 필드의 구분기호를 이곳에 적어줌
 
 
(A,B,C,….)
: 데이터가 있을 경우 컬럼 이름을 적어준다.
 
BEGIN DATA
A,B,C,…….
:실제 데이터 적어 준다.
 
 
SQL Loader 실행
$ sqlldr
아무 옵션없이 sqlldr 명령어만 실행하면 옵션들이 나옴.

 
 
 
 

---

 
 
 
SQL Loader 실습
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습1 시작
실습 1: 입력할 데이터가 외부 프로그램 자료일 경우 (ex. 엑셀)
 
 
1. 엑셀 자료 입력후 파일로 저장 : csv(쉼표로구분) 형식
                                                : dept2.csv
deptno  dname         loc
10      Accounting    New York
20      Research      Dallas
30      Sales         Chicago
40      Operations     Boston
 
 
2. 오라클에 해당 파일 옮기기 (경로 : /home/oracle/)
 
3. vi editor로 확인
 
4. 필요한 부분만 남기고 편집(데이터 부분만 남기고 나머지 삭제)
 
5. (오라클에서) 입력할 테이블 dept2 생성
SQL> conn scott/tiger
SQL> create table dept2 as select * from dept where 1=2;  ← 말도 안되는 조건을 줘서 형식만 가지고 오도록..
SQL> select * from dept2;
자료없음
 
6. SQL Loader 의 control file 생성
$ vi dept2.ctl
load data
infile "/home/oracle/dept2.csv"
into table dept2
fields terminated by ','
(deptno, dname, loc)
:wq!
 
7. SQL Loader 실행
$ sqlldr scott/tiger control='/home/oracle/dept2.ctl'
 
8. 결과 확인
SQL> select * from dept2;
 
    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 Accounting                   New York
        20 Research                     Dallas
        30 Sales                        Chicago
        40 Operations                   Boston
 
 

※ 참고
 : 실제 업무환경에서는 데이터가 많기 때문에, 값이 입력되는 중간에 에러가 발생할 화률이 크다.
   이 경우 에러가 발생한 부분을 찾아서 수정후 다시 입력 시켜야 한다.
 : 특별한 설정없이 작업내용이 log파일에 기록되어 진다. (Control file 과 같은 이름이고, 확장자만 .log로 생성)
$ vi dept2.log

 
 

※ 의문점
 : 만약 입력 도중 에러가 나서 다시 SQL Loader를 실행한다면??
 
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Feb 16 15:14:41 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
SQL*Loader-601: For INSERT option, table must be empty.  Error on table DEPT2
 
해당에러 발생 : 테이블이 비어 있어야만 INSERT 옵션이 실행된다.
                        controlfile의 내용을 바꾸어 적절한 옵션을 줘야 한다. (위 contorlfile 옵션부분 참조) 

 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습1 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습2 시작
실습 2: 입력 데이터가 control file 내부에 있을 경우 - 실제로 이런경우 99%없음.
 
1. dept2.ctl 파일 수정해서 dept2_02.ctl로 저장
 
$ vi dept2_02.ctl
load data
infile *
replace
into table dept20
fields terminated by ',' optionally enclosed by '"'
(deptno, dname, loc)
begindata
12,"서울점","강남구"
22,"대전점","유성구"
33,"제주점","서귀포시"
41,"서울본사","서울"
:wq!
 
 
2. 저장 후 테이블 생성
 
SYS> create table scott.dept20
  2  (deptno number, dname varchar2(10), loc varchar2(10));
 
$ sqlldr scott/tiger control='/home/oracle/dept2_02.ctl'
 
 
3. 결과 확인
SQL> set sqlprompt "_USER> "
 
SCOTT> select * from dept20;
 
 
※ 에러!!
 : 자료 입력 되었다고 나왔지만, 막상 select 해보면 자료가 없다.
 : 케릭터셋의 문제로 의심됨....
 
해결법 참고 : 
2012/02/16 - [Study/Oracle - 백업&복구] - 백업&복구 - SQL Loader 문자셋(character set)관련 - 문자셋이 맞지않아 SQL Loader 사용에 애로사항이 꽃핀다면??

미봉책으로 실습 진행을 위해 영어로 입력해 보았더니 잘 됨.
 
$ vi dept3.ctl
load data
infile *
replace
into table dept20
fields terminated by ','
(deptno, dname, loc)
begindata
12,Seoul,gangnamgu
22,Dae,Yu
33,Jeju,Seo
41,Seoulbon,seo
:wq!
 
$ sqlldr scott/tiger control='/home/oracle/dept3.ctl'
 
SQL*Loader: Release 10.2.0.5.0 - Production on Thu Feb 16 15:48:16 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Commit point reached - logical record count 4
 
 
SYS> select * from scott.dept20;
 
    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        12 Seoul                gangnamgu
        22 Dae                  Yu
        33 Jeju                 Seo
        41 Seoulbon             seo

 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습2 끝
 
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습3 시작
실습 3: 각종 파일 사용하기 (BAD File, DISCARD File 사용하기)
 : 수천 수만건 이상 되는 큰 데이터 파일에 오류가 있을경우
 
★ 전제조건
dept2 테이블 구조 변경
SQL> alter table dept2 modify deptno number(2);
 
 
1. Control file 내용변경
$ vi dept2_02.ctl
load data
infile *
append
into table dept2
fields terminated by ',' optionally enclosed by '"'
(deptno, dname, loc)
begindata
12,"서울점","강남구"
22,"대전점","유성구"
300,"제주점","서귀포시"
:wq!
 
→ deptno 2자리 까지 밖에 못 들어가는데 일부러 에러를 만들기위해 300으로 설정
 
 
2. 실행
 
$ sqlldr scott/tiger control='/home/oracle/dept2_02.ctl'
 
3. 테이블 조회
 
SCOTT> select * from dept2;
 
 

※ 에러!!
 : 자료 입력 되었다고 나왔지만, 막상 select 해보면 자료가 없다.
 : 케릭터셋의 문제로 의심됨....
 
해결법 참고 : 
2012/02/16 - [Study/Oracle - 백업&복구] - 백업&복구 - SQL Loader 문자셋(character set)관련 - 문자셋이 맞지않아 SQL Loader 사용에 애로사항이 꽃핀다면?? 
 


미봉책으로 실습 진행을 위해 영어로 입력해 보았더니 잘 됨.
 
$ vi dept3.ctl
load data
infile *
append
into table dept2
fields terminated by ','
(deptno, dname, loc)
begindata
12,Seoul,gangnamgu
22,Dae,Yu
300,Jeju,Seo
 
:wq!
 
$ sqlldr scott/tiger control='/home/oracle/dept3.ctl'
 
 
SCOTT> select * from dept2;
 
    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 Accounting                   New York
        20 Research                     Dallas
        30 Sales                        Chicago
        40 Operations                   Boston
        12 Seoul                        gangnamgu
        22 Dae                          Yu
 
6 rows selected.
 
: 예상대로 300,Jeju,Seo 부분이 누락됨
 
 
 
 
 
4. 로그조회
$ vi dept2.log
필요하부분만 보자면
 
Control File:   /home/oracle/dept3.ctl
Data File:      /home/oracle/dept3.ctl
  Bad File:     /home/oracle/dept3.bad ← 문제가 되는 내용 저장됨
 
Record 3: Rejected - Error on table DEPT2, column DEPTNO.
ORA-01438: value larger than specified precision allowed for this column
 
 
5. Bad 파일 확인
 
$ vi dept3.bad
300,Jeju,Seo
 
6. 에러 데이터 다시 입력
문제가 된 부분이 나와있다. Bad 파일을 수정해서 dat 파일로 저장 후 Control file 을 수정해서 위 파일 내용만 다시 입력시키면된다.
 
① Bad파일 형식에 맞게 수정
$ cp dept3.bad dept3.dat
$ vi dept3.dat
33,Jeju.Seo
:wq!
 
② Control file 수정
$ vi dept3.ctl
load data
infile "/home/oracle/dept3.dat"
append
into table dept2
fields terminated by ','
(deptno, dname, loc)
 
③ SQL Loader 실행해서 데이터 입력
$ sqlldr scott/tiger control='/home/oracle/dept3.ctl'
 
 
④ 입력확인
 
SCOTT> select * from dept2;
 
    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 Accounting                   New York
        20 Research                     Dallas
        30 Sales                        Chicago
        40 Operations                   Boston
        12 Seoul                        gangnamgu
        22 Dae                          Yu
        33 Jeju                         Seo
 
7 rows selected.

 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습3 끝
 
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습4 시작
실습 4: control file 에서 position 사용하기 (예제 데이터에 null 값도 포함)
 : 컨트롤파일 안에 직접 데이터 컬럼의 위치를 지정해 주는 방법 사용
 
 
1.엑셀 데이터 입력 : test3.prn (텍스트 - 공백으로 분리)로 저장 후 리눅스로 옮기기
 
사번    이름    급여    인센티브    부서번호
1000    나사장  1000        10
1001    나상무  700     20
1002    전부장  500 500 30
1003    기왕애  200 300 40
 
 
2. 리눅스에서 vi로 확인후 수정
$ vi test3.prn
 
사번 이름 급여 인센티브 부서번호
1000 나사장 1000    10
1001 나상무 700     20
1002 전부장 500 500 30
1003 기왕애 200 300 40
 
 
 
3. Control fle 수정
$ vi test3.ctl
load data
infile '/home/oracle/test3.prn'
into table test3
trailing nullcols
(empno          position(01:04) integer external,
name            position(6:11)  char,
position        position(13:16) char,
sal             position(18:21) integer external,
comm            position(23:25) integer external,
deptno          position(27:28) integer external)
 
참고 : trailing nullcols 부분이 없으면 null 값이 입력될때 에러 발생
 
 
4. Test3 테이블 생성
  1  create table scott.test3
  2  (empno number,
  3  name varchar2(10),
  4  position varchar2(10),
  5  sal number,
  6  comm number,
  7* deptno number)
 
 
 
5. SQL loader 수행
$ sqlldr scott/tiger control='test3.ctl'
 
6. test3 테이블 확인
SQL> select * from scott.test3;
 
 

※ 정리
 : control file에서 데이터들의 컬럼의 길이를 직접 position 이라는 파라미터를 이용하여 지정
 : 데이터에 한글이 있을경우 2bytes 문자이기 때문에 칼럼의 위치를 잘 지정해야 한다.
   → 잘 못 지정될 경우 데이터가 입력이 안되고 multibytes 에러가 난다.

 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습4 끝
 
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습5 시작
실습 5: null 값을 0 으로 표시하기
 
 
1. 엑셀데이터 입력후 저장(csv 쉼표로 분리) 후 리눅스로 이동 : test4.csv
 
번호 이름   급여 상여금
0001 홍길동 300   50
0002 이순신 500
0003 강감찬 250
0004 유관순 630
 
 
2. 리눅스에서 확인후 필요없는 부분 편집
$ vi test4.csv
0001,홍길동,300,50,
0002,이순신,500,,
0003,강감찬,250,,
0004,유관순,630,,
 
 
3. control file 생성
$ vi test4.ctl
load data
infile '/home/oracle/test4.csv'
replace
into table test4
fields terminated by ','
(번호 char,
 이름 char,
 급여 integer external,
 상여금 integer external "nvl(:상여금,0)")
:wq!
 
 
4. Test4 table 생성
SQL> create table scott.test4
  2  (번호 number(4),
  3   이름 varchar2(10),
  4   급여 number(4),
  5   상여금 number(4));
 
 
5. SQL Loader 를 수행해서 데이터 입력
$ sqlldr scott/tiger control='test4.ctl'
 
 
6. 데이터 확인
SQL> select * from test4;
 
 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 실습5 끝