sqlldr和external table的关系！

其实external table是对sqlldr工具的一个补充和完善！[@more@]

--创建目录
SQL> connect /as sysdba
已连接。
SQL> create or replace directory sqldr
2 as 'F:f_temporaclesqlldrtest6'
3 ;

目录已创建。
SQL> select * from dba_directories;

OWNER DIRECTORY_NAM DIRECTORY_PATH
---------- ------------- --------------------------------------------------
SYS SQLDR F:f_temporaclesqlldrtest6
SYS DATA_PUMP_DIR e:oracleproduct10.2.0adminorcldpdump
--给使用目录的用户授权
SQL> grant read , write on directory sqldr to xys;

授权成功。
--创建外部表（主要是语句organization external）
SQL> edit
已写入 file afiedt.buf

1 create table "users"
2 (username varchar2(30),
3 user_id number,
4 password varchar2(30)
5 )
6 organization external
7 (
8 type oracle_loader
9 default directory sqldr
10 access parameters
11 (
12 records delimited by newline characterset zhs16gbk
13 badfile 'users.bad'
14 discardfile 'users.dis'
15 logfile 'users.log'
16 readsize 1048576
17 fields terminated by "," optionally enclosed by '"' ldrtrim
18 missing field values are null
19 reject rows with all null fields
20 (
21 username char(30)
22 terminated by "," optionally enclosed by '"',
23 user_id char(30)
24 terminated by "," optionally enclosed by '"',
25 password char(30)
26 terminated by "," optionally enclosed by '"'
27 )
28 )
29 location('data.txt')
30 )
31* reject limit unlimited
SQL> /

表已创建。
--访问外部表数据
SQL> select * from xys."users";

USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
SCOTT 27 F894844C34402B67
XYS 28 FADC85CDC219C6A7
TEST 38 7A0F2B316C212D67
TSMSYS 21 3DF26A8B17D0F29F
DIP 19 CE4A36B8E06CA59C
DBSNMP 24 8A7084606AE5EB5C
SYSMAN 29 28F72A3C2D75FDE9
WMSYS 25 7C9BA362F8314299
MGMT_VIEW 31 B572AD376CE600CC
SYS 0 75800913E1B66343
SYSTEM 5 970BAA5B81930A40

USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
OUTLN 11 4A3BA55E08595C81

已选择12行。
--================================
--从上面外部表的创建语法我们清楚的发现它和sqlldr的语法非常的相似
--下面为sqlldr编写一个control file
load
infile 'F:f_temporaclesqlldrtest6data.txt'
badfile 'F:f_temporaclesqlldrtest6users.bad'
discardfile 'F:f_temporaclesqlldrtest6uesrs.dis'
append
into table test
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(
username char(30),
user_id char(30),
password char(30)
)
--================================
--使用sqlldr往上面创建的外部表"users"中插入数据
F:f_temporaclesqlldrtest6>sqlldr xys/manager control=user.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 20:17:56 2008

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

SQL*Loader-822: 外部组织的表不支持操作
--上面提示表明sqlldr不能往外部表中插入数据，不支持，更进一步说明外部表是只读的
--创建一个普通的heap table继续使用sqlldr插入数据
SQL> connect xys/manager
已连接。
SQL> create table test(
2 username varchar2(30),
3 user_id number,
4 password varchar2(30)
5 );

表已创建。

F:f_temporaclesqlldrtest6>sqlldr xys/manager control=user.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 20:19:20 2008

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

达到提交点 - 逻辑记录计数 12
--======================================
从上面测试过程我们可以更深刻的理解其实外部表就是对sqlldr的一个补充或者完善
从oracle9i开始sqlldr提供的参数external_table=generate_only可以通过
sqlldr生成创建外部表的语法，使用起来非常方便，但是生成的语法中需要把badfile,
discardfile,logfile前面的'SYS_SQLLDR_XT_TMPDIR_00000':去掉，否则创建外部表
时不会出现错误，但是执行select访问数据时提示错误：
SQL> select * from xys."users";
select * from xys."users"
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-04080: 未找到目录对象 SYS_SQLLDR_XT_TMPDIR_00000
ORA-06512: 在 "SYS.ORACLE_LOADER", line 19

SQL>
--====================================
测试通过sqlldr生成创建外部表的语法如下：
F:f_temporaclesqlldrtest6>sqlldr xys/manager control=user.ctl external_table
=generate_only

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 20:22:42 2008

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

--使用sqlldr生成创建外部表的语法如下：

SQL*Loader: Release 10.2.0.1.0 - Production on 星期六 7月 12 20:22:42 2008

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

控制文件: user.ctl
数据文件: F:f_temporaclesqlldrtest6data.txt
错误文件: F:f_temporaclesqlldrtest6users.bad
废弃文件: F:f_temporaclesqlldrtest6uesrs.dis
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
继续: 未作指定
所用路径: 外部表

表 TEST,已加载从每个逻辑记录
插入选项对此表 APPEND 生效
TRAILING NULLCOLS 选项生效

列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
USERNAME FIRST 30 , O (") CHARACTER
USER_ID NEXT 30 , O (") CHARACTER
PASSWORD NEXT 30 , O (") CHARACTER

文件需要 CREATE DIRECTORY 语句
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'F:f_temporaclesqlldrtest6'

用于外部表的 CREATE TABLE 语句:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"
(
"USERNAME" VARCHAR2(30),
"USER_ID" NUMBER,
"PASSWORD" VARCHAR2(30)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'users.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'uesrs.dis'
LOGFILE 'user.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"USERNAME" CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"USER_ID" CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PASSWORD" CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'data.txt'
)
)REJECT LIMIT UNLIMITED

用于加载内部表的 INSERT 语句:
------------------------------------------------------------------------
INSERT /*+ append */ INTO TEST
(
USERNAME,
USER_ID,
PASSWORD
)
SELECT
"USERNAME",
"USER_ID",
"PASSWORD"
FROM "SYS_SQLLDR_X_EXT_TEST"

用于清除由以前的语句创建的对象的语句:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_TEST"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

从 星期六 7月 12 20:22:42 2008 开始运行
在 星期六 7月 12 20:22:42 2008 处运行结束

经过时间为: 00: 00: 00.14
CPU 时间为: 00: 00: 00.08

--====================================

用法1：

--使用外部表访问alert文件：

SQL> create directory bdump as
2 'E:oracleproduct10.2.0admintestbdump'
3 ;

目录已创建。

SQL> create table alert_log(
2 log_text varchar2(4000)
3 )
4 organization external
5 (
6 type oracle_loader
7 default directory bdump
8 access parameters
9 (
10 records delimited by newline
11 nobadfile
12 nodiscardfile
13 nologfile
14 )
15 location('alert_test.log')
16 )
17 reject limit unlimited
18 /

表已创建。

SQL> select count(*) from alert_log;

COUNT(*)
----------
165510

SQL> SELECT COUNT(*) FROM ALERT_LOG WHERE LOG_TEXT LIKE 'ORA-%';

COUNT(*)
----------
57402

SQL>

用法2：

使用外部表访问listener log：

--首先确定listener.log的位置

SQL> host lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-7月 -2008 21:0
5:55

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

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xys)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 12-7月 -2008 18:39:56
正常运行时间 0 天 2 小时 26 分 2 秒
跟踪级别 off
安全性 ON: Password or Local OS Authentication
SNMP OFF
监听程序参数文件 e:oracleproduct10.2.0db_1networkadminlistener.o
ra
监听程序日志文件 e:oracleproduct10.2.0db_1networkloglistener.log

监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xys)(PORT=1521)))
服务摘要..
服务 "TEST1" 包含 1 个例程。
例程 "test", 状态 READY, 包含此服务的 1 个处理程序...
服务 "dmt" 包含 1 个例程。
例程 "dmt", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "orcl" 包含 1 个例程。
例程 "orcl", 状态 READY, 包含此服务的 2 个处理程序...
服务 "orcl_XPT" 包含 1 个例程。
例程 "orcl", 状态 READY, 包含此服务的 2 个处理程序...
服务 "test" 包含 2 个例程。
例程 "test", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
例程 "test", 状态 READY, 包含此服务的 1 个处理程序...
服务 "test_XPT" 包含 1 个例程。
例程 "test", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

SQL>
SQL> create directory listener_log
2 as
3 'e:oracleproduct10.2.0db_1networklog'
4 ;

目录已创建。

SQL> create table listener_log(
2 log_text varchar2(4000)
3 )
4 organization external(
5 type oracle_loader
6 default directory listener_log
7 access parameters
8 (
9 records delimited by newline
10 nobadfile
11 nologfile
12 nodiscardfile
13 )
14 location ('listener.log')
15 )
16 reject limit unlimited
17 /

表已创建。

SQL> select count(*) from listener_log;

COUNT(*)
----------
23977

SQL>

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

转载于:http://blog.itpub.net/19602/viewspace-1007176/