• 博客访问: 2945102
  • 博文数量: 2175
  • 用 户 组: 普通用户
  • 注册时间: 2008-03-30 17:16
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(2175)

文章存档

2013年(7)

2012年(29)

2011年(54)

2010年(61)

2009年(200)

2008年(274)

2007年(595)

2006年(557)

2005年(391)

2004年(5)

2003年(2)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

Oracle Database 12C中的SQL*Loader 新增加了Express Mode,借助这个特性,可以在最小化配置的情况下加载数据(比如无需要创建Control file)。下面通过一个简单示例快速感受下:
■创建测试表

     SYS% cdb1> conn study/study@cdb1pdb1
     Connected.
     STUDY% cdb1pdb1> create table test
              ( region      char(3),
                region_name varchar2(12),
                bill_month  number(6),
                fee         number(10,2)
              );

     Table created.
     STUDY% cdb1pdb1> 

■准备测试数据
     STUDY% cdb1pdb1> 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% cdb1pdb1> 

■用SQL*Loader Express Mode快速加载数据
     STUDY% cdb1pdb1> host sqlldr study/study@cdb1pdb1 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% cdb1pdb1> 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% cdb1pdb1> 

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

     STUDY% cdb1pdb1> 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% cdb1pdb1> 

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

 

[@more@]
阅读(3733) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册