外部表示例

建立目錄

CREATE OR REPLACE DIRECTORY data_dir AS '/home/oracle/direct';
CREATE OR REPLACE DIRECTORY bad_dir AS '/home/oracle/direct';
CREATE OR REPLACE DIRECTORY log_dir AS '/home/oracle/direct';      

.gz檔案外部表

CREATE TABLE salesz_ext (
  id  NUMBER
, txt CHAR(100))
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
 DEFAULT DIRECTORY data_dir
 ACCESS PARAMETERS
 ( RECORDS DELIMITED BY NEWLINE
   PREPROCESSOR data_dir:'unc.sh'
   CHARACTERSET US7ASCII
   BADFILE bad_dir: 'salesz%a_%p.bad'
   LOGFILE log_dir: 'salesz%a_%p.log'
   FIELDS TERMINATED BY '|'
   MISSING FIELD VALUES ARE NULL )
LOCATION ( data_dir:'sales_1.dat.gz',
           data_dir:'sales_2.dat.gz' ))
PARALLEL
REJECT LIMIT 10;

cat unc.sh
/bin/gunzip -c $1      

普通外部表

CREATE TABLE sales_ext (
  id  NUMBER
, txt CHAR(100))
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
 DEFAULT DIRECTORY data_dir
 ACCESS PARAMETERS
 ( RECORDS DELIMITED BY NEWLINE
   CHARACTERSET US7ASCII
   BADFILE bad_dir: 'sales%a_%p.bad'
   LOGFILE log_dir: 'sales%a_%p.log'
   FIELDS TERMINATED BY '|'
   MISSING FIELD VALUES ARE NULL )
LOCATION ( data_dir:'sales_1.dat',
           data_dir:'sales_2.dat' ))
PARALLEL
REJECT LIMIT 10;      

CREATE TABLE sales_stage (
  id  NUMBER
, txt CHAR(100));

alter session enable parallel dml;

INSERT /*+ APPEND PARALLEL(itab,2) */ INTO sales_stage itab SELECT /*+ PARALLEL(tab,2) */ * FROM sales_ext tab;