꿈꾸는 개발자, DBA 커뮤니티 구루비

1. External 테이블 설정


[oracle@mydream test]$ cat demo_ext.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Korea
20,Accounting,Japan
30,Consulting,America
40,Finance,China

-- 로그에 관련쿼리를 생성해 줌
[oracle@mydream test]$ sqlldr genie/genie demo_ext.ctl external_table=generate_only

-- 로그파일에서 내용확인
[oracle@mydream test]$ cat demo_ext.log

SQL*Loader: Release 11.2.0.1.0 - Production on 화 12월 30 04:17:05 2014

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

제어 파일:    demo_ext.ctl
데이터 파일:    demo_ext.ctl
  부적합한 파일:     demo_ext.bad
  폐기 파일:    지정 사항 없음

 (모든 폐기된 레코드 허용)

로드할 건수: ALL
생략 건수:  0
허용 오류수:  50
계속:    지정 사항 없음
사용된 경로:      외부 테이블

테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: INSERT

   열 이름                        위치    Len   Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER



파일에 CREATE DIRECTORY 명령문이 필요함
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/test'


외부 테이블의 CREATE TABLE 문:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  "DEPTNO" NUMBER(2),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(1000)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16MSWIN949
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo_ext.bad'
    LOGFILE 'demo_ext.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'demo_ext.ctl'
  )
)REJECT LIMIT UNLIMITED


INSERT 문이 내부 테이블 로드에 사용됨:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT
(
  DEPTNO,
  DNAME,
  LOC
)
SELECT
  "DEPTNO",
  "DNAME",
  "LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"


이전 명령문이 생성한 객체를 정리하기 위한 명령문:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000



화 12월 30 04:17:05 2014에 실행 개시
화 12월 30 04:17:06 2014에 실행 종료

경과 시간:        00:00:00.25
CPU 시간:         00:00:00.01

EXTERNAL_TABLE에 사용하는 파라미터
- NOT_USED : 사용하지 않음
- EXECUTE : EXTERNAL 테이블생성 및 데이터 로드
- GENERATE_ONLY : 로그파일에 DDL, DML문을 생성

디렉토리 생성: 파일 접근에 필요한 디렉토리 객체 생성


CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/test'

주의사항 : 디렉토리 생성은 오라클 계정으로 시도하기 때문에 접근하려는 os상의 디렉토리에 오라클 계정에서의 읽기, 쓰기 권한을 부여해야 한다.

External Table 생성 쿼리문


CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  "DEPTNO" NUMBER(2),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(1000)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16MSWIN949    -- 개행문자를 레코드의 끝으로 인식
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo_ext.bad'           --  실패처리된 레코드를 기록하는 파일명
    LOGFILE 'demo_ext.log_xt'               -- 로그파일
    READSIZE 1048576                   -- 입력 데이터 파일을 읽기 위해 사용하는 기본버퍼(여기서는 1MB, dedicated server에서는 PGA존재, shared server 에서는  SGA에 존재)
    SKIP 6          -- 건너 뛰어야할 레코드 수, 여기서는 INFILE *를 지정했기 때문.
    FIELDS TERMINATED BY "," LDRTRIM     -- 컬럼은 콤마(,)로 구분함. LDRTRIM은 Trim의 기본값, LRTRIM, LTRIM, RTRIM, NOTRIM이 있음
    REJECT ROWS WITH ALL NULL FIELDS  -- 로우 전체가 비어있으면, EXTERNAL 테이블에 로드되지 않고 BAD파일에 기록
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location                              -- 로드할 파일명 지정,
  (
    'demo_ext.ctl'
  )
)REJECT LIMIT UNLIMITED

*

데이터를 입력하는 방식


INSERT /*+ append */ INTO DEPT
(
  DEPTNO,
  DNAME,
  LOC
)
SELECT
  "DEPTNO",
  "DNAME",
  "LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"

SQLLDR가 생성한 임시객체 생성


DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

2. 오류처리

BADFILE을 이용하여 오류를 처리함


-- demo_ext.ctl 파일에 일부러 오류레코드를 넣음
[oracle@mydream test]$ cat demo_ext.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Korea
20,Accounting,Japan
30,Consulting,America
40,Finance,China
ABC,XYZ,Ihaveadream

-- 아직 BADFILE이 없음
[oracle@mydream test]$ ls -al demo_ext.bad
ls: demo_ext.bad: 그런 파일이나 디렉토리가 없음

-- external table 에서 쿼리를 실행함
SQL> select * from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 Sales          Korea
        20 Accounting     Japan
        30 Consulting     America
        40 Finance        China

-- BADFILE 이 생성되었음
[oracle@mydream test]$ ls -al demo_ext.bad
-rw-r--r-- 1 oracle dba 20 12월 30 06:03 demo_ext.bad

[oracle@mydream test]$ cat demo_ext.bad
ABC,XYZ,Ihaveadream

-- BAD 레코드를 처리할 수 있는 또다른 EXTERNAL 테이블을 생성
SQL> l
  1  create table et_bad
  2  ( text1 varchar2(4000),
  3    text2 varchar2(4000),
  4    text3 varchar2(4000)
  5  )
  6  organization external
  7  (type oracle_loader
  8   default directory SYS_SQLLDR_XT_TMPDIR_00000
  9    access parameters
 10    (
 11      records delimited by newline
 12      fields
 13      missing field values are null
 14      (
 15          text1 position(1:4000),
 16          text2 position(4001:8000),
 17          text3 position(8001:12000)
 18      )
 19    )
 20    location
 21    (
 22      'demo_ext.bad'
 23    )
 24* )
/
테이블이 생성되었습니다.

SQL> select * from et_bad;

TEXT1                          TEXT2                          TEXT3
------------------------------ ------------------------------ ------------------------------
ABC,XYZ,Ihaveadream

External Table에서의 project column referenced


SQL> select dname from SYS_SQLLDR_X_EXT_DEPT;

DNAME
--------------
Sales
Accounting
Consulting
Finance


SQL> select deptno from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO
----------
        10
        20
        30
        40


-- BADFILE에 있는 컬럼을 가져올 수 있게 한다.
SQL> alter table SYS_SQLLDR_X_EXT_DEPT project column referenced;

테이블이 변경되었습니다.

-- BADFILE에 있는 XYZ도 조회됨
SQL> select dname from SYS_SQLLDR_X_EXT_DEPT;

DNAME
--------------
Sales
Accounting
Consulting
Finance
XYZ

-- 아래쿼리와 위쿼리에서처럼 쿼리문에 따라 조회되는 row 수가 다를 수 있다.
SQL> select deptno from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO
----------
        10
        20
        30
        40

3. External 테이블을 사용해서 다른 파일을 로드


-- 동일한 포멧의 데이터 생성
[oracle@mydream test]$ cp demo_ext.ctl demo_ext2.ctl

-- external table의 외부파일 변경
SQL> alter table SYS_SQLLDR_X_EXT_DEPT location ('demo_ext2.ctl');

테이블이 변경되었습니다.

SQL> select * from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 Sales          Korea
        20 Accounting     Japan
        30 Consulting     America
        40 Finance        China

4. 다중 사용자 문제


-- 1개의 external 테이블에서 여러개의 파일을 로드할 때
SQL> alter table SYS_SQLLDR_X_EXT_DEPT location ('demo_ext.ctl', 'demo_ext2.ctl');

테이블이 변경되었습니다.

SQL> select * from SYS_SQLLDR_X_EXT_DEPT;

    DEPTNO DNAME          LOC
---------- -------------- --------------------------------------------------
        10 Sales          Korea
        20 Accounting     Japan
        30 Consulting     America
        40 Finance        China
        10 Sales          Korea
        20 Accounting     Japan
        30 Consulting     America
        40 Finance        China

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


-- 다중 사용자를 위한  bad, log 파일명 구분
-- ㅇ %p : PID 
-- ㅇ %a  = 병렬 실행 서버 에이전트ID(예: 001, 002 ... )
    RECORDS DELIMITED BY NEWLINE CHARACTERSET KO16MSWIN949
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo_%p.bad'            -- PID 단위로 bad파일이 생성됨
    LOGFILE 'demo_ext.log_xt'

14S_External 테이블 (by jiginet) [2015.01.26]

1. External 테이블 설정

2. 오류처리

3. External 테이블을 사용해서 다른 파일을 로드

4. 다중 사용자 문제