其實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

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

達到提交點 - 邏輯記錄計數 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

控制檔案: 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

正在連線到 (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>

sqlldr和external table的關係！

相關文章