ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用sqlloader控制文件生成外部表创建语句的一个例子

使用sqlloader控制文件生成外部表创建语句的一个例子

原创 Linux操作系统 作者:eric0435 时间:2013-10-06 20:09:28 0 删除 编辑

使用sqlldr的控制文件生成创建外部表的语句
先创建一个目录

SQL> create or replace directory dir1 as '/home/oracle';

Directory created

根据sqlldr的控制文件来生成创建外部表的语句,先查看SQLLDR向DEPT表中加载批量数据的例子,如下:

[oracle@jy ~]$ cat demo21.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

以下SQLLDR命令会为我们的外部表生成CREATE TABLE语句:

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo21.ctl external_table=generate_only

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013

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

EXTERNAL_TABLE参数有以下3个值:
NOT_USED:这是默认值.
EXECUTE:这个值说明SQLLDR不会生成并执行一个SQL INSERT语句;而是会创建一个外部表,并使用一个批量SQL语句来加载.
GENERATE_ONLY:这个值使得SQLLDR并不具体加载任何数据,而只是会生成所执行的SQL DDL和DML语句,并放到它创建的日志文 件中.
但是要注意:DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY.如果指定了DIRECT=TRUE,则会加载数据,而不会生成外部表.

使用GENERATE_ONLY时,可以在demo21.log文件中看到以下内容:

[oracle@jy ~]$ cat demo21.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013

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

Control File:   demo21.ctl
Data File:      demo21.ctl
  Bad File:     demo21.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 DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  NEXT     *   ,       CHARACTER

下面为外部表生成CREATE TABLE语句:

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  "DEPTNO" NUMBER(2),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(14)
)

SQLLDR已经登录到数据库;只有这样它才知道这个外部表定义中要用的具体数据类型(例如,DEPTNO是一个NUMBER(2)). SQLLDR根据数据字典来确定这些数据类型.

ORGANIZATION EXTERNAL子句告诉Oracle:这不是一个"正常"表.

ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'DIR1':'demo21.bad'
    LOGFILE 'demo21.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'demo21.ctl'
  )
)REJECT LIMIT UNLIMITED

ORACEL_LOADER类型是目前支持的两种类型之一(Oracle9i中只支持这一种类型),另一种类型是ORACLE_DATAPUMP,
这是Oracle 10g及以上版本中Oracle的专用数据泵格式.这种格式不仅可以用于加载数据,也可以卸载数据.

这些访问参数显示了如何建立一个外部表,使之能像SQLLDR一样几乎以同样的方式处理文件:
RECORDS:记录默认以换行符结束,SQLLDR中的记录就是如此.

BADFILE:在刚创建的目录中建立了一个坏文件(无法处理的记录都记录到这个文件中).

LOGFILE:在当前的工作目录中记录了一个等价于SQLLDR日志文件的日志文件.

READSIZE:这是Oracle读取输入数据文件所用的默认缓冲区.在这里是1MB.如果采用专用服务器模式,这个内存来自PGA,如果 采用共享服务器模式,则来自SGA,它用于缓存输入数据文件中对应一个会话的信息.

SKIP 6:在确定了应该跳过输入文件中的多少记录.你可能会问:为什么有skip 6.是这样,在这个例子中使用了INFILE *;使用SKIP 6就是跳过控制文件本身来得到内嵌的数据.如果没有使用INFILE *,就根本不会有SKIP子句.

FIELDS TERMINATED BY:这与控制文件中的用法一样.不过,外部表增加LDRTRIM,这代表Loader TRIM.这是一种截断模式,模 拟了SQLLDR截断数据的默认做法.还有另外一些选项,包括LRTRIM,LTRIM和RTRIM,表示左截断/右截断空白符;NOTRIM表示保 留所有前导/尾随的空白符.

REJECT ROWS WITH ALL NULL FIELDS:这导致外部表会在坏文件中记录所有全空的行,而且不加载这些行.

列定义本身:这是有关所期望输入数据值的元数据.它们是所加载数据文件中的字符串,长度最多可达255个字符(SQLLDR的默 认大小),以逗号(,)结束,还可以选择用引号括起来.

location部分告诉Oracle所加载文件的文件名,在这里就是demo21.ctl,因为我们在原控制文件中使用了INFILE *.控制文件中的下一条语句是默认的INSERT,可以用于从外部表本身加载表:

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT
(
  DEPTNO,
  DNAME,
  LOC
)
SELECT
  "DEPTNO",
  "DNAME",
  "LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT"

如果可能的话,这会执行一个逻辑上与直接路径加载等价的操作(如果可以遵循APPEND提示;如果存在触发器或外键约束,可能 不允许发生直接路径操作).

最后,在日志文件中,我们会看到一些语句,这些语句可以用于删除加载完成之后SQLLDR我我们创建的对象:

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


可能会看到日志文件中有一个CREATE DIRECTORY语句(也可能看不到).在生成外部表脚本期间,SQLLDR连接到数据库,并查询 数据字典来查看是否已经存在合适的目录.在这个例子中,由于有合适的目录,所以SQLLDR为我们选择了我们之前所创建的目录DIR1

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'

通过sqldr生成外部全创建语句的完整语句如下:

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
(
  "DEPTNO" NUMBER(2),
  "DNAME" VARCHAR2(14),
  "LOC" VARCHAR2(14)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'DIR1':'demo21.bad'
    LOGFILE 'demo21.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "DEPTNO" CHAR(255)
        TERMINATED BY ",",
      "DNAME" CHAR(255)
        TERMINATED BY ",",
      "LOC" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'demo21.ctl'
  )
)REJECT LIMIT UNLIMITED;

执行创建外部表的语句:

jy@JINGYONG> CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"
  2  (
  3    "DEPTNO" NUMBER(2),
  4    "DNAME" VARCHAR2(14),
  5    "LOC" VARCHAR2(14)
  6  )
  7  ORGANIZATION external
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY DIR1
 11    ACCESS PARAMETERS
 12    (
 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
 14      BADFILE 'DIR1':'demo21.bad'
 15      LOGFILE 'demo21.log_xt'
 16      READSIZE 1048576
 17      SKIP 6
 18      FIELDS TERMINATED BY "," LDRTRIM
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        "DEPTNO" CHAR(255)
 22          TERMINATED BY ",",
 23        "DNAME" CHAR(255)
 24          TERMINATED BY ",",
 25        "LOC" CHAR(255)
 26          TERMINATED BY ","
 27      )
 28    )
 29    location
 30    (
 31      'demo21.ctl'
 32    )
 33  )REJECT LIMIT UNLIMITED;

表已创建。

然后通过所创建的外部表向dept表插入数据:

jy@JINGYONG> INSERT /*+ append */ INTO DEPT
  2  (
  3    DEPTNO,
  4    DNAME,
  5    LOC
  6  )
  7  SELECT
  8    "DEPTNO",
  9    "DNAME",
 10    "LOC"
 11  FROM "SYS_SQLLDR_X_EXT_DEPT";

已创建4行。

jy@JINGYONG> commit;

提交完成。

创建了日志文件

[oracle@jy ~]$ ls -lrt demo21.log_xt
-rw-r--r-- 1 oracle oinstall 737 Oct  5 00:43 demo21.log_xt

SQL> select * from jy.dept;

DEPTNO DNAME          LOC            ENTIRE_LINE  LAST_UPDATED COMMENTS
------ -------------- -------------- ------------ ------------ ---------
    10 Sales          Virginia
    20 Accounting     Virginia
    30 Consulting     Virginia
    40 Finance        Virginia

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

请登录后发表评论 登录
全部评论
系统架构师,ACOUG MEMBER,Oracle ACE,Mail:yongjing.star@gmail.com Mobile:13875984558 QQ:409898894 Blog:www.jydba.net

注册时间:2011-10-12

  • 博文量
    537
  • 访问量
    6184729