第一步、建立directory，給使用者授權，並建立sqlldr匯入的目標表結構
$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 17 15:17:58 2007

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

[@more@]

SQL> create directory external_table_dir as '/oracle/sqlldr/';

Directory created.

SQL> grant read,write on directory external_table_dir to shiyihai;

Grant succeeded.

SQL> conn shiyihai/xxx
Connected.
SQL> create table sqlldr_service
2 (
3 msisdn varchar2(11),
4 date_time varchar2(14),
5 icpcode varchar2(12),
6 icpservid varchar2(32)
7 );

Table created.

SQL>

第二步、準備sqlldr
源目標資料格式如下：
$ cat temp_card_user.txt
13945444444|20051109010851|908800|-MFZQ00006
13766805555|20051109072654|908800|-KZW001019
13766866666|20051109072712|908800|-KZW001019
13766877777|20051109073337|908811|-LT0001001
13766888888|20051109074009|908811|-MFZQ00003
13766999999|20051109074631|908800|-MFZQ00006
13766000000|20051109074922|908800|-KZW001022
13633611111|20051109091954|908814|-NW0001002
sqlldr格式control檔案如下：
$ cat temp_card_user.ctl
load data
infile '/oracle/sqlldr/temp_card_user.txt'
Append
into table sqlldr_service
fields terminated by '|'
TRAILING NULLCOLS
(
msisdn,
date_time,
icpcode,
icpservid
)
呼叫的shell命令如下：
$ cat data2db.sh
sqlldr userid=shiyihai/xxx control=/oracle/sqlldr/temp_card_user.ctl log=/oracle/sqlldr/temp_card_user.log

第三步、呼叫sqlldr並生成外部表的表結構
如下是呼叫過程：
$
$ ./data2db.sh
sh: ./data2db.sh: Execute permission denied.
$ chmod a+x data2db.sh
$ ll
total 16
-rwxr-xr-x 1 oracle dba 73 Aug 17 15:33 data2db.sh
-rw-r--r-- 1 oracle dba 0 Aug 17 15:30 temp_card_user.bad
-rw-r--r-- 1 oracle dba 193 Aug 17 15:36 temp_card_user.ctl
-rw-r--r-- 1 oracle dba 2296 Aug 17 15:26 temp_card_user.txt
$ pwd
/oracle/sqlldr
$ ./data2db.sh

SQL*Loader: Release 9.2.0.6.0 - Production on Fri Aug 17 15:46:59 2007

Commit point reached - logical record count 9
$
$ sqlplus "shiyihai/xxx"

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Aug 17 15:47:27 2007

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from sqlldr_service;

MSISDN DATE_TIME ICPCODE ICPSERVID
----------- -------------- ------------ --------------------------------
13945444444 20051109010851 908800 -MFZQ00006
13766805555 20051109072654 908800 -KZW001019
13766866666 20051109072712 908800 -KZW001019
13766877777 20051109073337 908811 -LT0001001
13766888888 20051109074009 908811 -MFZQ00003
13766999999 20051109074631 908800 -MFZQ00006
13766000000 20051109074922 908800 -KZW001022
13633611111 20051109091954 908814 -NW0001002

8 rows selected.

SQL>
ok，到這裡我們可以看到sqlldr執行成功！生成的log檔案如下：
$ vi temp_card_user.log
"temp_card_user.log" 52 lines, 1780 characters

SQL*Loader: Release 9.2.0.6.0 - Production on Fri Aug 17 15:55:10 2007

Control File: /oracle/sqlldr/temp_card_user.ctl
Data File: /oracle/sqlldr/temp_card_user.txt
Bad File: /oracle/sqlldr/temp_card_user.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table SQLLDR_SERVICE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MSISDN FIRST * | CHARACTER
DATE_TIME NEXT * | CHARACTER
ICPCODE NEXT * | CHARACTER
ICPSERVID NEXT * | CHARACTER

Record 9: Discarded - all columns null.

Table SQLLDR_SERVICE:
8 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
1 Row not loaded because all fields were null.

Space allocated for bind array: 66048 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 9
Total logical records rejected: 0
Total logical records discarded: 1

Run began on Fri Aug 17 15:55:10 2007
Run ended on Fri Aug 17 15:55:10 2007

Elapsed time was: 00:00:00.18
CPU time was: 00:00:00.06

接下來我們從sqlldr的生成日誌中來提取生成外部表的指令碼，見如下的操作日誌：
$ cp data2db.sh create_external_table_script.sh
對create_external_table_script.sh進行編輯後結果如下：
$ cat create_external_table_script.sh
sqlldr userid=shiyihai/xxx control=/oracle/sqlldr/temp_card_user.ctl log=/oracle/sqlldr/create_external_table_script.sql external_table=GENERATE_ONLY
$ ./create_external_table_script.sh

SQL*Loader: Release 9.2.0.6.0 - Production on Fri Aug 17 16:00:07 2007

$ ll
total 28
-rwxr-xr-x 1 oracle dba 154 Aug 17 15:57 create_external_table_script.sh
-rw-r--r-- 1 oracle dba 2930 Aug 17 16:00 create_external_table_script.sql
-rwxr-xr-x 1 oracle dba 111 Aug 17 15:54 data2db.sh
-rw-r--r-- 1 oracle dba 0 Aug 17 15:30 temp_card_user.bad
-rw-r--r-- 1 oracle dba 194 Aug 17 15:54 temp_card_user.ctl
-rw-r--r-- 1 oracle dba 1780 Aug 17 15:55 temp_card_user.log
-rw-r--r-- 1 oracle dba 361 Aug 17 15:42 temp_card_user.txt
$
$ cat create_external_table_script.sql

SQL*Loader: Release 9.2.0.6.0 - Production on Fri Aug 17 16:00:07 2007

Control File: /oracle/sqlldr/temp_card_user.ctl
Data File: /oracle/sqlldr/temp_card_user.txt
Bad File: /oracle/sqlldr/temp_card_user.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 SQLLDR_SERVICE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
MSISDN FIRST * | CHARACTER
DATE_TIME NEXT * | CHARACTER
ICPCODE NEXT * | CHARACTER
ICPSERVID NEXT * | CHARACTER

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

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_SQLLDR_SERVIC"
(
MSISDN VARCHAR2(11),
DATE_TIME VARCHAR2(14),
ICPCODE VARCHAR2(12),
ICPSERVID VARCHAR2(32)
)
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':'temp_card_user.bad'
LOGFILE '/oracle/sqlldr/create_external_table_script.sql_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
MSISDN CHAR(255)
TERMINATED BY "|",
DATE_TIME CHAR(255)
TERMINATED BY "|",
ICPCODE CHAR(255)
TERMINATED BY "|",
ICPSERVID CHAR(255)
TERMINATED BY "|"
)
)
location
(
'temp_card_user.txt'
)
)REJECT LIMIT UNLIMITED

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO SQLLDR_SERVICE
(
MSISDN,
DATE_TIME,
ICPCODE,
ICPSERVID
)
SELECT
MSISDN,
DATE_TIME,
ICPCODE,
ICPSERVID
FROM "SYS_SQLLDR_X_EXT_SQLLDR_SERVIC"

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_SQLLDR_SERVIC"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Run began on Fri Aug 17 16:00:07 2007
Run ended on Fri Aug 17 16:00:07 2007

Elapsed time was: 00:00:00.46
CPU time was: 00:00:00.09
$
ok，從中我們提取建立external table的指令碼並稍作整理如下：

CREATE TABLE external_service
(
MSISDN VARCHAR2(11),
DATE_TIME VARCHAR2(14),
ICPCODE VARCHAR2(12),
ICPSERVID VARCHAR2(32)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY external_table_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK --裝載的記錄以行為標誌，字符集為ZHS16GBK
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS --忽略空行
(
MSISDN CHAR(255)
TERMINATED BY "|",
DATE_TIME CHAR(255)
TERMINATED BY "|",
ICPCODE CHAR(255)
TERMINATED BY "|",
ICPSERVID CHAR(255)
TERMINATED BY "|"
)
)
location
(
'temp_card_user.txt'
)
)REJECT LIMIT UNLIMITED --預設為0，UNLIMITED代表忽略所有異常
tablespace shiyihai;

Table created

SQL> select * from external_service;

MSISDN DATE_TIME ICPCODE ICPSERVID
----------- -------------- ------------ --------------------------------
13945444444 20051109010851 908800 -MFZQ00006
13766805555 20051109072654 908800 -KZW001019
13766866666 20051109072712 908800 -KZW001019
13766877777 20051109073337 908811 -LT0001001
13766888888 20051109074009 908811 -MFZQ00003
13766999999 20051109074631 908800 -MFZQ00006
13766000000 20051109074922 908800 -KZW001022
13633611111 20051109091954 908814 -NW0001002

8 rows selected

SQL>
SQL> select TABLESPACE_NAME from user_tables where table_name='EXTERNAL_SERVICE';

TABLESPACE_NAME
------------------------------
SYSTEM

SQL> select * from user_segments where segment_name='EXTERNAL_SERVICE';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_POOL
-------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -----------

SQL> select * from user_extents where segment_name='EXTERNAL_SERVICE';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
-------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ----------

SQL>

最後，總結一下，從上面的結果我們可以看出幾個地方：
1、即使在建立表時指定了表空間，external table的表結構仍然建在system表空間上；
2、external table的資料不在資料檔案上，故沒有segment和extent，只在資料庫中儲存一個表結構定義；
3、external table和sqlldr比較相似，但外部表有很多侷限性，例如不支援索引、LOB型別等等，而且訪問路徑中的檔案必須在伺服器端；
4、10g開始，外部表可支援insert操作，並且TYPE可指定為oracle_loader或ORACLE_DATAPUMP，這是兩種不同的驅動，另外可產生多個外部檔案；
5、外部表和sqlldr可靈活運用在dataware、BI的etl上，還有系統資料介面應用上，絕對是個好幫手，另外也可將某些日誌檔案掛鉤到外部表後靈活運用sql語句可產生很多有實際意義的監控資訊來，比如alert_SID.log等。

sqlldr與external table

相關文章