外部表数据归档配置和解释

利用external table归档数据

1.sys用户下建立directory                                                     --放数据文本

sql>create directory ora16 as '/u01/dab/histdata/';                          --建立directory

sql>grant execute,read,write on directory ora16 to oqy;                      --授予oqy权限


2.oqy用户下建立外部表

sql>CREATE TABLE "TM_YJSM_EXT"
(
  "DTIME" DATE,
  "T_SJ" DATE,
  "T_SORT" DATE,
  "TXM" VARCHAR2(20),
  "QS" VARCHAR2(20),
  "SMLB" VARCHAR2(20),
  "SFD" VARCHAR2(20),
  "DD" VARCHAR2(20),
  "XJDD" VARCHAR2(20),
  "DBTXM" VARCHAR2(20),
  "WPLB" VARCHAR2(20),
  "WPZL" VARCHAR2(20),
  "SMY" VARCHAR2(20),
  "BZ" VARCHAR2(600),
  "SRC" VARCHAR2(20),
  "PARTID" CHAR(2)
)
ORGANIZATION external
(
  TYPE oracle_loader                                                         --用sql*loader引擎加载
  DEFAULT DIRECTORY ora16                                                    --指定文本位置
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET utf8                           --行数据范围和字符集
    BADFILE 'ORA16':'a0301_bas_2011-05-0620.bad'                             --指定放有问题数据的文件
    LOGFILE 'ORA16':'a06_yjsm_2011-05-0620.log_xt'                           --指定操作日志位置
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM              --分隔符
    MISSING FIELD VALUES ARE NULL                                            --null为缺省值
    REJECT ROWS WITH ALL NULL FIELDS                                         --不能有全空行
    (                                                                        --字段类型
      "DTIME" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        DATE_FORMAT DATE MASK "YYYY-MM-DD HH24-MI-SS",
      "T_SJ" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        DATE_FORMAT DATE MASK "YYYY-MM-DD HH24-MI-SS",
      "T_SORT" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        DATE_FORMAT DATE MASK "YYYY-MM-DD HH24-MI-SS",
      "TXM" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "QS" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SMLB" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SFD" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "DD" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "XJDD" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "DBTXM" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "WPLB" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "WPZL" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SMY" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "BZ" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "SRC" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "PARTID" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    ora16:'a0301_bas_2011-05-0620.csv'                                        --指定具体文本文件
  )
)REJECT LIMIT UNLIMITED;                                                      --取消最大问题数据的限制