数据库表--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,

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1063061/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1063061/