External Table

외부 파일 조회와 내부 테이블 적재의 차이, 두 드라이버의 Load와 Unload 처리 순서

ORACLE_LOADERACCESS PARAMETERSORACLE_DATAPUMPLoadUnload

#핵심 요약

외부 테이블의 실제 데이터는 OS 파일에 있고 DB에는 컬럼, 파일 위치, 드라이버 메타데이터가 저장됨

SELECT만으로 내부 적재가 되지는 않음
내부 테이블 CTAS 또는 INSERT SELECT를 별도로 수행하는 흐름임

#개념과 동작 원리

구분	ORACLE_LOADER	ORACLE_DATAPUMP
입력 파일	CSV 등 텍스트	전용 이진 파일
파일 조회	가능	가능
Unload	지원하지 않는다고 설명	외부 테이블 CTAS로 조회 결과 기록
생성 후 파일 수정	일반 INSERT, UPDATE, DELETE 불가	일반 INSERT, UPDATE, DELETE 불가

ORGANIZATION EXTERNAL 안에서 TYPE, DEFAULT DIRECTORY, LOCATION을 정함
LOADER 실습은 ACCESS PARAMETERS로 줄 구분, 쉼표 구분, 누락 필드 NULL, 날짜 마스크를 정의함

DATAPUMP 실습은 소스 외부 테이블 CTAS로 EMP를 파일에 내보내고, 타깃으로 파일을 복사한 뒤 컬럼 구조를 맞춰 외부 테이블을 생성함
이 흐름에서는 expdp, impdp 대신 SQL로 추출, 조회하며 파일 복사는 별도임

#필수 명령어 또는 SQL

SQL*Plus (타깃 외부 테이블 소유 계정)조회만 함

SELECT COUNT(*) FROM emp_ext;

결과 해석: 외부 파일을 읽은 행수 확인이며 숫자는 기재 없음

SQL*Plus (타깃 외부 테이블 소유 계정)객체 또는 설정을 변경함

CREATE TABLE emp_load AS SELECT * FROM emp_ext;

SQL*Plus (타깃 외부 테이블 소유 계정)조회만 함

SELECT COUNT(*) FROM emp_load;

결과 해석: 실습에서는 내부 테이블을 생성한 뒤 행수를 조회하는 단계까지 제시함(확인 불가)
실제 적재 건수 출력과 실습 대비 일치 결과는 기재 없음

#ORACLE_LOADER로 텍스트 파일 읽기

파일 (/home/oracle/scott_dir/emp1.txt)객체 또는 설정을 변경함

7369,SMITH,CLERK,7902,1980-12-17,800,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,30
7566,JONES,MANAGER,7839,1981-04-02,2975,20

oracle 계정 셸객체 또는 설정을 변경함

mkdir -p /home/oracle/scott_dir
chmod 755 /home/oracle/scott_dir
ls -l /home/oracle/scott_dir/emp1.txt

SQL*Plus (SYSDBA)객체 또는 설정을 변경함

CREATE OR REPLACE DIRECTORY scott_dir AS '/home/oracle/scott_dir';
GRANT READ, WRITE ON DIRECTORY scott_dir TO scott;
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'SCOTT_DIR';

SQL*Plus (scott)객체 또는 설정을 변경함

CREATE TABLE emp1_ext (
  empno    NUMBER(4),
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  deptno   NUMBER(2)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY scott_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      empno,
      ename,
      job,
      mgr,
      hiredate CHAR(10) DATE_FORMAT DATE MASK "YYYY-MM-DD",
      sal,
      deptno
    )
  )
  LOCATION ('emp1.txt')
)
REJECT LIMIT UNLIMITED;

SELECT * FROM emp1_ext ORDER BY empno;
SELECT COUNT(*) FROM emp1_ext;

항목	뜻
`TYPE ORACLE_LOADER`	텍스트 파일을 읽는 엔진
`DEFAULT DIRECTORY`	파일이 있는 DIRECTORY 객체
`RECORDS DELIMITED BY NEWLINE`	한 줄이 한 행
`FIELDS TERMINATED BY ','`	쉼표로 컬럼 구분
`MISSING FIELD VALUES ARE NULL`	줄 끝에 빠진 필드는 NULL
`DATE_FORMAT DATE MASK`	날짜 문자열을 읽을 형식
`LOCATION`	읽을 파일 이름

결과 해석: 파일 네 줄이 네 행으로 조회되면 정상이며, 형식이 맞지 않는 줄은 REJECT LIMIT UNLIMITED 때문에 오류 없이 빠지므로 같은 디렉터리에 생기는 로그, bad 파일을 함께 봄
ORACLE_DATAPUMP 방식으로 내보내고 다른 DB에서 읽는 예는 외부 테이블 활용에 있음

#사용 조건과 주의사항

DIRECTORY는 OS 폴더를 생성하지 않음
DB READ/WRITE 권한과 서버 프로세스의 OS 접근 권한을 각각 준비함

외부 테이블의 인덱스, 트리거 생성과 일반 DML은 지원하지 않는다고 실습에서는 설명함
DROP 후에도 원래 파일은 남는다는 설명이 있음

REJECT LIMIT UNLIMITED 예제에서는 거부된 행이 조회에서 빠질 수 있어 로그, 오류 파일도 확인함

데이터 이동만으로 실습의 인덱스, 제약조건, 트리거, 권한까지 자동 이행되지 않음