外部表（三）--解读通过sql*loader生成的创建外部表的日志

[oracle@secdb1 ~]$ vi t.log

-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL*Loader: Release 10.2.0.1.0 - Production on Mon Apr 1 19:11:17 2013

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

----------------------------------第一部分---------------------列举出sql*loader加载的控制文件部分----------------------------------------------------------------

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

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

Table T, 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
X6                                   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_T"
(
  "X1" VARCHAR2(20),
  "X2" VARCHAR2(20),
  "X3" VARCHAR2(20),
  "X4" VARCHAR2(20),
  "X5" VARCHAR2(20),
  "X6" VARCHAR2(20)
)
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.dec'
    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 '"',
      "X6" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    't.dat'
  )
)REJECT LIMIT UNLIMITED

 

-----------------------------------第四部分--------通过insert语句将数据转移到数据库的内部表中------------------------------------------------------------

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO T
      "X5" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "X6" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    't.dat'
  )
)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO T
(
  X1,
  X2,
  X3,
  X4,
  X5,
  X6
)
SELECT
  "X1",
  "X2",
  "X3",
  "X4",
  "X5",
  "X6"
FROM "SYS_SQLLDR_X_EXT_T"

 

----------------------------------第五部分------------------------最后完成整个过程，外部表和目录可以被删除----------------------------------------------------------------------------------

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

 

Run began on Mon Apr 01 19:11:17 2013
Run ended on Mon Apr 01 19:11:18 2013

Elapsed time was:     00:00:00.09
CPU time was:         00:00:00.02

 

不得不佩服，oracle真是太贴心了！一套实验过程都给写好了！