重庆思庄Oracle、Redhat认证学习论坛

标题: 12c sql*loader快捷模式,不用控制文件也可以加载数据 [打印本页]

作者: 郑全 时间: 2016-7-19 22:52
标题: 12c sql*loader快捷模式,不用控制文件也可以加载数据
本帖最后由郑全于 2016-7-19 22:56 编辑

12C中的SQL*Loader 新增加了Express Mode
Oracle Database 12C中的SQL*Loader 新增加了Express Mode，借助这个特性，可以在最小化配置的情况下加载数据（比如无需要创建Control file)。
下面通过一个简单示例快速感受下：
◆ 创建测试表
   SYS% cdb1> conn study/study@pdb1
   Connected.
   STUDY% pdb1> create table test
            ( region    char(3),
            region_name varchar2(12),
            bill_month  number(6),
            fee       number(10,2)
            );
   Table created.
   STUDY% pdb1>

◆ 准备测试数据
   STUDY% pdb1> host cat test.dat
   530,HZ,200501,100.01
   530,HZ,200502,800.23
   531,JN,200501,5000.81
   531,JN,200502,5360.00
   532,QD,200501,20670.32
   532,QD,200502,22000.08
   533,ZB,200501,3050.56
   533,ZB,200502,3108.14

   STUDY% pdb1>

◆ 用SQL*Loader Express Mode快速加载数据
   STUDY% pdb1> host sqlldr study/study@pdb1 TABLE=test --是不是很简单

   SQL*Loader: Release 12.1.0.1.0 - Production on Sun Jun 30 14:05:36 2013
   Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

   Express Mode Load, Table: TEST
   Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
   Table TEST:
            8 Rows successfully loaded.

   Check the log files:
            test.log
            test_%p.log_xt
   for more information about the load.

◆ 数据加载完毕,查看数据
   STUDY% pdb1> select * from test;

   REG REGION_NAME BILL_MONTH FEE
   --- -------------- ------------ ----------
   530 HZ          200501    100.01
   530 HZ          200502    800.23
   531 JN          200501    5000.81
   531 JN          200502    5360
   532 QD          200501    20670.32
   532 QD          200502    22000.08
   533 ZB          200501    3050.56
   533 ZB          200502    3108.14

   8 rows selected.

   STUDY% pdb1>

◆ 工作原理可以从日志文件中看到

   STUDY% pdb1> host cat test.log

   SQL*Loader: Release 12.1.0.1.0 - Production on Sun Jun 30 14:05:36 2013

   Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

   Express Mode Load, Table: TEST
   Data File:    test.dat
   Bad File:    test_%p.bad
   Discard File:  none specified

   (Allow all discards)

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

   Table TEST, loaded from every logical record.
   Insert option in effect for this table: APPEND

      Column Name                Position Len  Term Encl Datatype
   ------------------------------ ---------- ----- ---- ---- ---------------------
   REGION                            FIRST    * ,    CHARACTER
   REGION_NAME                         NEXT    * ,    CHARACTER
   BILL_MONTH                         NEXT    * ,    CHARACTER
   FEE                               NEXT    * ,    CHARACTER

   Generated control file for possible reuse:
   OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
   LOAD DATA
   INFILE 'test'
   APPEND
   INTO TABLE TEST
   FIELDS TERMINATED BY ","
   (
   REGION,
   REGION_NAME,
   BILL_MONTH,
   FEE
   )
   End of generated control file for possible reuse.

   created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle

   enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

   creating external table "SYS_SQLLDR_X_EXT_TEST"

   CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"
   (
   "REGION" CHAR(3),
   "REGION_NAME" VARCHAR2(12),
   "BILL_MONTH" NUMBER(6),
   "FEE" NUMBER(10,2)
   )
   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':'test_%p.bad'
      LOGFILE 'test_%p.log_xt'
      READSIZE 1048576
      FIELDS TERMINATED BY "," LRTRIM
      REJECT ROWS WITH ALL NULL FIELDS
      (
         "REGION" CHAR(255),
         "REGION_NAME" CHAR(255),
         "BILL_MONTH" CHAR(255),
         "FEE" CHAR(255)
      )
   )
   location
   (
      'test.dat'
   )
   )REJECT LIMIT UNLIMITED

   executing INSERT statement to load database table TEST

   INSERT /*+ append parallel(auto) */ INTO TEST
   (
   REGION,
   REGION_NAME,
   BILL_MONTH,
   FEE
   )
   SELECT
   "REGION",
   "REGION_NAME",
   "BILL_MONTH",
   "FEE"
   FROM "SYS_SQLLDR_X_EXT_TEST"

   dropping external table "SYS_SQLLDR_X_EXT_TEST"

   Table TEST:
   8 Rows successfully loaded.

   Run began on Sun Jun 30 14:05:36 2013
   Run ended on Sun Jun 30 14:05:43 2013

   Elapsed time was:    00:00:06.61
   CPU time was:       00:00:00.05

   STUDY% pdb1>

◆ 说明
（1）这里有个需要注意的地方，上面的命令行中的表名大小写一定要和操作系统上对应的文件名大小写一样，比如TABLE=TEST 那么对应的存放数据文件要是TEST.dat,如果TABLE=test,那么对应的存放数据文件要是test.dat  (文件扩展名必须是.dat)
（2）数据文件必须是逗号分隔的SQL*Loader和external tables支持的格式。
（3）更多关于SQL*Loader Express Mode的介绍，参见官方手册。

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)