使用sqlloader控制文件生成外部表创建语句的一个例子
转自 http://blog.itpub.net/26015009/viewspace-773790/
使用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
