External Tables

conn /as sysdba;

create or replace directory ext as 'c:\external\'

grant read on ext to hr;
grant wrtie on ext to hr;

conn hr/hr
-- 예제 : oracle_datapump
drop table ext_employees;
create table ext_employees
organization external (
 type oracle_datapump
 default directory ext
 location('emp.ext')
)
as
select * from employees;

CREATE TABLE <table_name> (
<column_name, column_name, ...>)
ORGANIZATION EXTERNAL
(TYPE oracle_datapump
DEFAULT DIRECTORY <oracle_directory_object_name>
LOCATION ('<file_name'))
[PARALLEL]
AS
<SQL Statement>;

-- 예제 : oracle_loader

The file empxt1.dat contains the following sample data:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

The file empxt2.dat contains the following sample data:

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

CREATE TABLE admin_ext_employees
                   (employee_id       NUMBER(4),
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25),
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_dat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile 'empxt%a_%p.bad'
         logfile 'empxt%a_%p.log'
         fields terminated by ','
         missing field values are null
         ( employee_id, first_name, last_name, job_id, manager_id,
           hire_date char date_format date mask "dd-mon-yyyy",
           salary, commission_pct, department_id, email
         )
       )
       LOCATION ('empxt1.dat', 'empxt2.dat')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;

CREATE TABLE <table_name> (
<column_definitions>)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
 DEFAULT DIRECTORY <oracle_directory_object_name>
 ACCESS PARAMETERS (
  RECORDS DELIMITED BY newline
  BADFILE <file_name>
  DISCARDFILE <file_name>
  LOGFILE <file_name>
  [READSIZE <bytes>]
  [SKIP <number_of_rows>
  FIELDS TERMINATED BY '<terminator>'
  REJECT ROWS WITH ALL NULL FIELDS
  MISSING FIELD VALUES ARE NULL
  ( <column_name_list>))\
   LOCATION ('<file_name>'))
[PARALLEL]
REJECT LIMIT <UNLIMITED | integer>;

Changing the target file

ALTER TABLE <table_name> LOCATION ('<file1>', '<file2>', ....);

SELECT * FROM ext_tab;

ALTER TABLE ext_tab LOCATION ('demo1.dat');

SELECT * FROM ext_tab;

ALTER TABLE ext_tab LOCATION ('demo1.dat', 'demo2.dat');

SELECT * FROM ext_tab;

ALTER TABLE ext_tab LOCATION ('demo2.dat');

참조 : http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2448