ITPub博客

首页 > 数据库 > Oracle > Oracle SQL*Loader

Oracle SQL*Loader

Oracle 作者:地铁上小虫 时间:2015-11-03 11:52:35 0 删除 编辑

一、SQL*Loader的体系结构

    SQL*Loader由一个输入控制文件来控制整个装载的相关描述信息,一个或多个数据文件作为原始数据,其详细组成结构包括

        Input Datafiles     -->装载到数据库的原始数据文件

        Loader Control file  -->提供给QL*Loader寻找及翻译数据的相关信息

        Log File             -->装载过程中产生的日志信息

        Bad Files          -->被剔除的一些不合乎规范化的数据,由SQL*Loader剔除,也可能被Oracle剔除

        Discard Files       -->对不满足控制文件中记录选择标准的一些物理记录

        以上五个完整的部分最终将数据导入到数据库,当然,部分组件可以省略。

 

二、控制文件的作用及组成

    控制文件是一个文本文件,控制文件中记录的信息告诉SQL*Loader在哪里寻找数据、如何翻译数据,以及将数据插入到哪里等

    控制文件的组成分为三个部分

        第一部分主要是关于通外部会话的相关信息

            如一些全局选项、行信息、是否跳过特殊记录等

            infile子句指明了从哪里寻找源数据

        第二部分由一个或多个Intotable块,每一个块包含一些被导入表的相关信息,如表名,列名等

        第三部分为可选项,如果存在则包含导入的源数据

    控制文件写法的注意事项

        语法结构自由

        不区分大小写

        在行开始处使用--来作为注释行,在控制文件中的第三部分使用--来注释不被支持

        关键字constant 和zone被保留

 

三、数据文件

    数据文件可以有多个,这些数据文件需要在控制文件中指定

    从SQL*Loader角度来看,数据文件中的数据被当做一条条记录

    一个数据文件描述数据文件记录有三种可选的格式

        固定记录格式

        可变记录格式

        流记录格式

    这些记录格式在控制文件使用infile参数时,如果记录的格式未指定,则缺省的为流记录格式。如使用infile*时则为流记录格式

   

    下面给出几种不同记录格式的例子

        a.固定格式:INFILE datafile_name "fix n"

 

            load data

            infile 'example.dat'  "fix 11"   --表明每条记录长度固定为个字节

            into table example

            fields terminatedby ',' optionally enclosedby '"'

            (col1, col2)

 

            example.dat:

            001,   cd, 0002,fghi, --第一条记录为,   cd, 第二条记录为,fghi, 其中第二条记录包含了一个换行符

            00003,lmn,

            1, "pqrs",

            0005,uvwx,

           

        b.可变格式:INFILE "datafile_name" "var n"

            load data

            infile 'example.dat'  "var 3"    --使用3位来描述一条记录的长度

            into table example

            fields terminatedby ',' optionally enclosedby '"'

            (col1 char(5),

             col2 char(7))

 

            example.dat:

            009hello,cd,010world,im, --009 表明第一条记录的长度为个9字节,表明第二条记录的长度为10个字节等

            012my,nameis,

 

       

        c.流记录格式:INFILE datafile_name ["str terminator_string"] 

            load data

            infile 'example.dat'  "str '|/n'"   --使用|或换行符来作为一条记录的终止

            into table example

            fields terminatedby ',' optionally enclosedby '"'

            (col1 char(5),

             col2 char(7))

 

            example.dat:

            hello,world,|

            james,bond,|

   

    逻辑记录的概念

        通常情况下,数据文件中的一条物理记录即是一条逻辑记录,即数据文件的一条记录对应于数据库的一条记录SQL*Loader扩展了该功能可以将多条物理记录形成一条逻辑记录,而由这个组合再来生成一条数据库中的记录

        SQL*Loader支持两种策略来形成逻辑记录

            组合固定条数的物理记录来形成逻辑记录

            将满足特定条件的物理记录组合并形成逻辑记录

 

四、数据文件装载方式   

    1.传统路径导入

        使用生成SQL Insert语句来处理源数据,并且通过commit提交保存数据。每次数据导入将产生一些事务

        在插入数据时寻找可用数据块,然后将数据填充到数据块

        在插入到分区表的单个分区时使用下面的语法

            INSERT INTO TABLE TPARTITION (P)VALUES ...

        基于多cpu系统使用多个装载会话执行并发。即将数据文件分割为多个来装载

       

    2.直接路径导入

        直接将数据写到Oracle数据文件,并更所使用块的高水位线标记来保存数据

        支持数据的并行导入

        直接路径导入期间,数据转换发生在客户端而非服务器端。即位于服务器端参数文件中NLS参数不会被使用

            可以通过在控制文件中设置NLS参数或设置服务器端合适的环境变量,如下面的例子

            HIREDATE DATE 'YYYYMMDD'               --为控制文件中的HIREDATE指定格式

            % export NLS_DATE_FORMAT='YYYYMMDD'   --在服务器端设定NLS_DATE_FORMAT

        直接路径装载单个分区或子分区,装载期间的其它分区可以执行DML操作

            LOAD INTO TABLE TPARTITION (P)VALUES ...

            LOAD INTO TABLE T SUBPARTITION(P) VALUES ...

        使用直接路径装载时,需要指定DIRECT=true

        支持两种不同的并发

            1.同时装载到分区表表的不同分区或同时装载到不同的表

            2.分成多个服务器装载到分区表的单个分区或单个表,最后将装载的临时段合并保存到分区或表

   

    3.两者对比(下面描述中传统导入方式称为前者,直接导入方式称为后者)

        a.前者使用commit来保存数据,后者更新高水位线标记保存数据

        b.前者产生redo记录,后者基于特定的条件产生redo记录

        c.前者强制所有的约束,后者仅仅强制primarykey,unique,notnull约束

        d.前者将触发insert触发器,后者不会触发insert触发器

        e.前者支持簇表,后者不支持簇表

        f.前者插入数据时其它用户可以DML表,后者则不行

       

五、演示SQL*Loader 

    1.SQL*Loader可执行程序(sqlldr)所在的位置

    [oracle@vmoel5u4 ~]$ ls -lh $ORACLE_HOME/bin/sql*
    -rwxr-x--x 1 oracle oinstall 634K Mar 24  2012 /u01/app/oracle/product/10.2.0/db_1/bin/sqlldr
 

    2.查看sqlldr的帮助信息,

        [oracle@oradb ~]$ sqlldr

 

        SQL*Loader: Release 10.2.0.1.0- Production on Thu Sep 23 10:38:31 2010

 

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

 

 

        Usage: SQLLDR keyword=value [,keyword=value,...]

 

        Valid Keywords:

 

            userid -- ORACLE username/password           

           control -- control file name                 

               log -- log file name                     

               bad -- bad file name                     

              data -- data file name                    

           discard -- discard file name                 

        discardmax -- number of discards to allow          (Default all)

              skip -- number of logical records to skip    (Default 0)

              load -- number of logical records to load    (Default all)

            errors -- number of errors to allow            (Default 50)

              rows -- number of rows in conventional path bind array or between direct path data saves

                       (Default: Conventionalpath 64, Direct path all)

          bindsize -- size of conventional path bind array in bytes  (Default 256000)

            silent -- suppress messages during run (header,feedback,errors,discards,partitions)

            direct -- use direct path                      (Default FALSE)

           parfile -- parameter file: name of file that contains parameter specifications

          parallel -- do parallel load                     (Default FALSE)

              file -- file to allocate extents from     

        skip_unusable_indexes-- disallow/allow unusable indexes or index partitions (Default FALSE)

        skip_index_maintenance-- do not maintain indexes, mark affected indexes as unusable (Default FALSE)

        commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)

          readsize -- size of read buffer                  (Default 1048576)

        external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)

        columnarrayrows -- number of rows for direct path column array  (Default 5000)

        streamsize -- size of direct path stream buffer in bytes  (Default 256000)

        multithreading -- use multithreading in direct path 

         resumable -- enable or disable resumable for current session  (Default FALSE)

        resumable_name -- text string to help identify resumable statement

        resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)

        date_cache -- size (in entries) of date conversion cache  (Default 1000)

 

        PLEASE NOTE: Command-line parameters may be specified eitherby

        position or by keywords.  An exampleof the former caseis 'sqlldr

       scott/tiger foo'; an example of the latter is 'sqlldr control=foo

       userid=scott/tiger'.  One may specify parametersby position before

        but not after parameters specified by keywords. For example,

        'sqlldr scott/tiger control=foo logfile=log' is allowed, but

        'sqlldr scott/tiger control=foo log' is not, even though the

        position of the parameter 'log' is correct.

       

    3.将数据文件和控制文件组合在一起

[oracle@vmoel5u4 ~]$ vi car.ctl1
LOAD DATA
INFILE *
INTO TABLE car
FIELDS TERMINATED BY ','
(maker, model, no_cyl,
first_built_date date "yyyy/mm/dd",
engine, hp,price)
BEGINDATA
Talbot,8/18,4,1923/02/03,ohv,8,295.00
Talbot,10/23,4,1923/03/04,ohv,8.9,375.00
Talbot,12/30,6,1924/01/04,ohv,13.4,550.00
Sunbeam,14/40,4,1924/06/23,ohv,13.9,895.00
Sunbeam,12/30,4,1924/02/28,ohv,11.5,570.00
Sunbeam,20/60,6,1924/02/24,ohv,20.9,950.00
Sunbeam,Twin Cam,6,1926/03/23,ohv,20.9,1125.00
Sunbeam,20,6,1927/03/23,ohv,20.9,750.00
Sunbeam,16,6,1927/09/10,ohv,16.9,550.00
Peugeot,172,4,1928/09/28,sv,6.4,165.00
Austin,7,4,1922/01/22,sv,7.2,225.00
Austin,12,4,1922/01/01,sv,12.8,550.00
Austin,20,4,1916/01/04,sv,22.4,616.00
Lanchester,40,6,1919/01/08,ohv,38.4,1875.00
Lanchester,21,6,1924/01/26,ohv,20.6,950.00
Vauxhall,30/98,4,1919/01/08,sv,23.8,1475.00
Vauxhall,23/60,4,1919/01/27,sv,22.4,1300.00
               

SQL> conn hr/hr
Connected.
create table car(
maker varchar2(20), 
model varchar2(20), 
no_cyl varchar2(20),
first_built_date date,
engine varchar2(20),
hp number,
price number(10,2));

Table created.

 

[oracle@vmoel5u4 ~]$ sqlldr hr/hr control=car.ctl1

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 28 22:25:41 2013

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

Commit point reached - logical record count 17

 

[oracle@vmoel5u4 ~]$ sqlplus hr/hr  

SQL> select count(*) from car;

  COUNT(*)
----------
        17

 

    4.将数据文件和控制文件分离实现数据装载

[oracle@vmoel5u4 ~]$ cat car.txt
Talbot,8/18,4,1923/02/03,ohv,8,295.00
Talbot,10/23,4,1923/03/04,ohv,8.9,375.00
Talbot,12/30,6,1924/01/04,ohv,13.4,550.00
Sunbeam,14/40,4,1924/06/23,ohv,13.9,895.00
Sunbeam,12/30,4,1924/02/28,ohv,11.5,570.00
Sunbeam,20/60,6,1924/02/24,ohv,20.9,950.00
Sunbeam,Twin Cam,6,1926/03/23,ohv,20.9,1125.00
Sunbeam,20,6,1927/03/23,ohv,20.9,750.00
Sunbeam,16,6,1927/09/10,ohv,16.9,550.00
Peugeot,172,4,1928/09/28,sv,6.4,165.00
Austin,7,4,1922/01/22,sv,7.2,225.00
Austin,12,4,1922/01/01,sv,12.8,550.00
Austin,20,4,1916/01/04,sv,22.4,616.00
Lanchester,40,6,1919/01/08,ohv,38.4,1875.00
Lanchester,21,6,1924/01/26,ohv,20.6,950.00
Vauxhall,30/98,4,1919/01/08,sv,23.8,1475.00
Vauxhall,23/60,4,1919/01/27,sv,22.4,1300.00

[oracle@vmoel5u4 ~]$ cat car.ctl2
LOAD DATA
infile '/home/oracle/car.txt'
APPEND
INTO TABLE hr.car
fields terminated by ","
(maker, model, no_cyl,
first_built_date date "yyyy/mm/dd",
engine, hp,price)
 

[oracle@vmoel5u4 ~]$ sqlldr hr/hr control=car.ctl2

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 28 22:31:40 2013

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

Commit point reached - logical record count 17

导入成功!

 

SQL> conn hr/hr
Connected.
SQL> select count(*) from car;

  COUNT(*)
----------
        34

由此可以看见有一次append17条记录进去了。 

Using SQL*Loader to create an external table

下面的实验是有一个txt的文本文件,根据此文本文件,使用SQL*Loader创建一个External Table.

1,创建控制文件

[oracle@vmoel5u4 ~]$ vi car.control 
load data
infile 'car.txt'
badfile 'car.bad'
discardfile 'car.discard'
append
into table car_info_test
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
 (
maker,
model,
no_cyl,
first_built_date date 'yyyy/mm/dd',
engine,
hp,
price
)

 

2,然后根据控制文件创建一个外部表

[oracle@vmoel5u4 ~]$ sqlldr oltp_usr/oracle control=car.control external_table=GENERATE_ONLY log=cardata.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sun Mar 31 19:05:06 2013

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

 

 

3,通过cardata.log文件来查看创建external table的语法:

[oracle@vmoel5u4 ~]$ vi cardata.log
      "PRICE" CHAR(255)
        TERMINATED BY ","
    )
Data File:      car.txt
  Bad File:     car.bad
  Discard File: car.discard
 (Allow all discards)

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

Table CAR, 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
------------------------------ ---------- ----- ---- ---- ---------------------
MAKER                               FIRST     *   ,       CHARACTER
MODEL                                NEXT     *   ,       CHARACTER
NO_CYL                               NEXT     *   ,       CHARACTER
FIRST_BUILT_DATE                     NEXT     *   ,       DATE yyyy/mm/dd
ENGINE                               NEXT     *   ,       CHARACTER
HP                                   NEXT     *   ,       CHARACTER
PRICE                                NEXT     *   ,       CHARACTER

 

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_CAR"
(
  "MAKER" VARCHAR2(20),
  "MODEL" VARCHAR2(20),
  "NO_CYL" NUMBER,
  "FIRST_BUILT_DATE" DATE,
  "ENGINE" VARCHAR2(20),
  "HP" NUMBER(10,1),
  "PRICE" NUMBER(10,2)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY TEST
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'TEST':'car.bad'
    DISCARDFILE 'TEST':'car.discard'
    LOGFILE 'cardata.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "MAKER" CHAR(255)
        TERMINATED BY ",",
      "MODEL" CHAR(255)
        TERMINATED BY ",",
      "NO_CYL" CHAR(255)
        TERMINATED BY ",",
      "FIRST_BUILT_DATE" CHAR(255)
        TERMINATED BY ","
        DATE_FORMAT DATE MASK "yyyy/mm/dd",
      "ENGINE" CHAR(255)
        TERMINATED BY ",",
      "HP" CHAR(255)
        TERMINATED BY ",",
      "PRICE" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'car.txt'
  )
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
[oracle@vmoel5u4 ~]$ vi cardata.log
        TERMINATED BY ",",
      "PRICE" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'car.txt'
  )
)REJECT LIMIT UNLIMITED


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO CAR
(
  MAKER,
  MODEL,
  NO_CYL,
  FIRST_BUILT_DATE,
  ENGINE,
  HP,
  PRICE
)
SELECT
  "MAKER",
  "MODEL",
  "NO_CYL",
  "FIRST_BUILT_DATE",
  "ENGINE",
  "HP",
  "PRICE"
FROM "SYS_SQLLDR_X_EXT_CAR"


statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_CAR"

 

由上面cardata.log文件的信息,可以看出,创建external table的语法都完整的给出了,只要稍做修改就可以创建外部表了

4, 创建外部表

SQL> conn hr/hr
Connected.
CREATE TABLE HR.car_info_test
(
  "MAKER" VARCHAR2(20),
  "MODEL" VARCHAR2(20),
  "NO_CYL" NUMBER,
  "FIRST_BUILT_DATE" DATE,
  "ENGINE" VARCHAR2(20),
  "HP" NUMBER(10,1),
  "PRICE" NUMBER(10,2)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY TEST
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'TEST':'car.bad'
    DISCARDFILE 'TEST':'car.discard'
    LOGFILE 'cardata.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "MAKER" CHAR(255)
        TERMINATED BY ",",
      "MODEL" CHAR(255)
        TERMINATED BY ",",
      "NO_CYL" CHAR(255)
        TERMINATED BY ",",
      "FIRST_BUILT_DATE" CHAR(255)
        TERMINATED BY ","
        DATE_FORMAT DATE MASK "yyyy/mm/dd",
      "ENGINE" CHAR(255)
        TERMINATED BY ",",
      "HP" CHAR(255)
        TERMINATED BY ",",
      "PRICE" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'car.txt'
  )
)REJECT LIMIT UNLIMITED;

Table created.

5,确认是否创建外部表成功

SQL> select count(*) from car_info_test;

  COUNT(*)
----------
        17



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

请登录后发表评论 登录
全部评论

注册时间:2014-05-12

  • 博文量
    49
  • 访问量
    155488