測試TOM=SQLLDR生成外部表

什麼是外部表--簡單說就是可以把作業系統檔案當成是一個只讀的資料庫表

下面測試利用SQLLDR來生成外部表

測試開始

[oracle@oraclelinux ~]$ cat demo1.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT_LOAD

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

BEGINDATA

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

[oracle@oraclelinux ~]$ sqlldr scott/scott control=demo1.ctl external_table=generate_only;

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 16:51:06 2012

以上部分為外部表進行了定義，檢視日誌檔案可以發現實際的SQL語句是怎麼樣的

[oracle@oraclelinux ~]$ cat demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 16:51:06 2012

Control File: demo1.ctl

Data File: demo1.ctl

Bad File: demo1.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_LOAD, 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 DIRECTORY statements needed for files

------------------------------------------------------------------------

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/u01/oracle'【在生成外部表指令碼期間，SQLLDR連線到資料庫，並查詢資料字典檢視是否已經存在合適的目錄，在這裡因為沒有合適的目錄所以生成了一個CREATE DIRECTORY語句】

CREATE TABLE statement for external table:

------------------------------------------------------------------------

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_LOAD"

(

"DEPTNO" NUMBER(2),

"DNAME" VARCHAR2(14),

"LOC" VARCHAR2(13)

) 【這是生成的建立外部表的語句】

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000【這一部分通知資料庫表不是正常表，不是普通的HEAP表，也不是IOT表，是外部表】

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad'

LOGFILE 'demo1.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

(

'demo1.ctl'

)

)REJECT LIMIT UNLIMITED【這通知資料庫所載入的檔名是什麼，這裡指DEMO1.CTL】

INSERT statements used to load internal tables:

------------------------------------------------------------------------

INSERT /*+ append */ INTO DEPT_LOAD

(

DEPTNO,

DNAME,

LOC

)

SELECT

"DEPTNO",

"DNAME",

"LOC"

FROM "SYS_SQLLDR_X_EXT_DEPT_LOAD"[這一部分是生成的INSERT語句，可用於從外部表本身載入資料]

statements to cleanup objects created by previous statements:

------------------------------------------------------------------------

DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_LOAD"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000【載入完成之後刪除這前建立的一些物件】

Run began on Mon May 14 16:51:06 2012

Run ended on Mon May 14 16:51:06 2012

Elapsed time was: 00:00:00.48

CPU time was: 00:00:00.07

[oracle@oraclelinux ~]$

接下來為SCOTT使用者分配適當許可權，測試外部表

SQL> show user;

USER is "SYS"

SQL> grant create any directory to scott;

Grant succeeded.

SQL> grant drop any directory to scott;

Grant succeeded.

SQL> select * from dept_load;

no rows selected

從外部表載入資料到DEPT_LOAD;

[oracle@oraclelinux ~]$ sqlldr scott/scott control=demo1.ctl external_table=execute;

SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 14 17:45:30 2012

[oracle@oraclelinux ~]$ exit

exit

SQL> select * from dept_load;

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA

---------- -------------- ------------- ----------------------------- ---------

COMMENTS

--------------------------------------------------------------------------------

10 Sales Virginia

20 Accounting Virginia

30 Consulting Virginia

DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDA

---------- -------------- ------------- ----------------------------- ---------

COMMENTS

--------------------------------------------------------------------------------

40 Finance Virginia

SQL>

測試結束

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-723555/，如需轉載，請註明出處，否則將追究法律責任。

測試TOM=SQLLDR生成外部表

相關文章