使用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