資料庫表--external table

解除安裝表資料到檔案並重新讀取

建立目錄
JEL@JEL >create directory dir_dmp as '/home/oracle';

Directory created.

JEL@JEL >grant read,write on directory dir_dmp to public;

Grant succeeded.

解除安裝資料
JEL@JEL >select * from test;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

JEL@JEL >create table t1 organization external (type oracle_datapump default directory dir_dmp
  2  location ('t1_1.dat','t1_2.dat')) parallel 2 as select * from test;

Table created.

JEL@JEL >select * from t1;

         X          Y
---------- ----------
         1          1
         2          2
         3          3

[oracle@jel ~]$ pwd
/home/oracle
[oracle@jel ~]$ ls
 t1_2.dat    t1_1.dat      temp

透過strings可以檢視檔案內容
載入資料
JEL@JEL >create table t2 (x int,y int) organization external (type oracle_datapump default directory dir_dmp location ('t1_1.dat','t1_2.dat'));

Table created.

JEL@JEL >select * from t2;

         X          Y
---------- ----------
         1          1
         2          2
         3          3


使用SQL*loader建立外部表

建立資料檔案，控制檔案
t.dat

1,1,1,1,1,
2,2,2,2,2,
3,3,
a,a,a,
#####################
t.ctl

load data
infile '/home/oracle/t.dat'
badfile '/home/oracle/t.bad'
discardfile '/home/oracle/t.dsc'
append
into table jel.t3
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(x1,x2,x3,x4,x5)
################################

建立表
JEL@JEL >create table t3 (x1 int,x2 int,x3 int,x4 int,x5 int);

Table created.

匯入
[oracle@jel ~]$ sqlldr userid=jel/jel control=/home/oracle/t.ctl external_table=generate_only log=/home/oracle/t.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Dec 12 12:17:20 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


[oracle@jel ~]$ more t.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Dec 12 12:17:20 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   /home/oracle/t.ctl
Data File:      /home/oracle/t.dat
  Bad File:     /home/oracle/t.bad
  Discard File: /home/oracle/t.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table JEL.T3, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X1                                  FIRST     *   ,  O(") CHARACTER            
X2                                   NEXT     *   ,  O(") CHARACTER            
X3                                   NEXT     *   ,  O(") CHARACTER            
X4                                   NEXT     *   ,  O(") CHARACTER            
X5                                   NEXT     *   ,  O(") CHARACTER            



CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/'---------與下面對應


CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_T3" ---------------修改成要建立的使用者的對應表名
(
  "X1" NUMBER(38),
  "X2" NUMBER(38),
  "X3" NUMBER(38),
  "X4" NUMBER(38),
  "X5" NUMBER(38)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000        ----------與上面對應
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.bad'
    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.dsc'
    LOGFILE 't.log_xt' --------------去掉絕對路徑
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "X1" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X2" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X3" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X4" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X5" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    't.dat'
  )
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO JEL.T3
(
  X1,
  X2,
  X3,
  X4,
  X5
)
SELECT
  "X1",
  "X2",
  "X3",
  "X4",
  "X5"
FROM "SYS_SQLLDR_X_EXT_T3"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_T3"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000



Run began on Thu Dec 12 12:17:20 2013
Run ended on Thu Dec 12 12:17:20 2013

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.02


建立外部表
JEL@JEL >CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/';
JEL@JEL >CREATE TABLE sqlldr_external
(
  "X1" NUMBER(38),
  "X2" NUMBER(38),
  "X3" NUMBER(38),
  "X4" NUMBER(38),
  "X5" NUMBER(38)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.bad'
    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t.dsc'
    LOGFILE 't.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "X1" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X2" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X3" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X4" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X5" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    't.dat'
  )
)REJECT LIMIT UNLIMITED;

JEL@JEL >select *from sqlldr_external;

        X1         X2         X3         X4         X5
---------- ---------- ---------- ---------- ----------
         1          1          1          1          1
         2          2          2          2          2
         3          3

此處，沒有載入資料檔案中的最後一行，是因為型別不正確，被放到了t.bad檔案中
[oracle@jel ~]$ more t.bad
a,a,a,