Oracle外部表學習

外部表即儲存在Oracle資料庫外部的表.實際上是Oracle提供的一種能夠通過驅動介面訪問外部檔案的一種形式.
到Oracle 10G以後,有兩種驅動方式可以使用外部表.

第一種是ORACLE_LOADER.這是一種傳統的方式,與SQLLDR很相似,裡面的命令引數也大致相同.
下面是君三思書中提供的例子
create directory testdir as '/home/oracle/testdb/testdir';
grant read,write on directory testdir to charsi;

create table ext_case1
(ename varchar2(10),
job varchar2(20),
sal number)
organization external
(type oracle_loader
default directory testdir
access parameters
(records delimited by newline
skip 6
fields terminated by ","
(ENAME,JOB,SAL)
)
LOCATION('ldr_case1.ctl')
);

[oracle:/home/oracle/testdb/testdir#]cat ldr_case1.ctl
LOAD DATA
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891

SQL> select * from ext_case1;

ENAME JOB SAL
---------- -------------------- ----------
SMITH CLEAK 3904
ALLEN SALESMAN 2891

下面是我試驗的例子
create table OLC9_ACC_BONUS
(
ACCOUNT_REF NUMBER(15),
BONUS_AMOUNT NUMBER(20)
)
organization external ----
(type oracle_loader 這部分是介紹外部表的內容的
default directory testdir ----
access parameters
(records delimited by newline
fields terminated by "," 這部分的內容類似於SQLLDR中的控制檔案部分
(ACCOUNT_REF,
BONUS_AMOUNT)
) ----
location('BMCRB2_650_64S_A_OLC9_ACC_BONUS.dump') ---指明外部表中的資料檔案的位置
);

SQL> select * from OLC9_ACC_BONUS where rownum <10;

ACCOUNT_REF BONUS_AMOUNT
----------- ------------
355239614 0
355229718 0
355724164 0
354695480 0
355239694 0
355254021 0
355239014 0
355239001 0
354695506 0

9 rows selected.

第二種驅動方式是ORACLE_DATAPUMP.資料泵(datapump),這是Oracle 10GR2中新增的資料訪問方式.這種方式感覺可以用來作為資料遷移.

我們首先來演示資料匯出
我們需要將select owner,object_name from all_objects的資料匯出到檔案,可以用下面的命令
create table ext_test_ACC_BONUS organization external
(
type oracle_datapump
default directory testdir
location ('ext_test_ACC_BONUS.dmp')
)
as select owner,object_name from all_objects;

執行上面的語句之後,就會在$(testdir)目錄下生成一個ext_test_ACC_BONUS.dmp檔案

然後我們匯入用下面的SQL：
create table imp_test_acc_bonus (
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30)
)
organization external
(type oracle_datapump
default directory testdir
location ('ext_test_ACC_BONUS.dmp')
);

生成一個imp_test_acc_bonus表,裡面的資料是我們當時匯出的資料(即select owner,object_name from all_objects的資料).

*********************************
對於使用第一種方式出現下面的這種錯誤:
SQL> select * from OLC9_ACC_BONUS;
select * from OLC9_ACC_BONUS
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

[oracle:/home/oracle/testdb/testdir#]cat OLC9_ACC_BONUS_3436.log

LOG file opened at 04/09/11 13:38:26

Field Definitions for table OLC9_ACC_BONUS
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

ACCOUNT_REF CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
BONUS_AMOUNT CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field BONUS_AMOUNT
KUP-04023: field start is after end of record
KUP-04101: record 7 rejected in file /home/oracle/testdb/testdir/OLC9_ACC_BONUS.ctl
KUP-04021: field formatting error for field BONUS_AMOUNT
KUP-04023: field start is after end of record
KUP-04101: record 8 rejected in file /home/oracle/testdb/testdir/OLC9_ACC_BONUS.ctl
KUP-04021: field formatting error for field ACCOUNT_REF
KUP-04023: field start is after end of record
KUP-04101: record 9 rejected in file /home/oracle/testdb/testdir/OLC9_ACC_BONUS.ctl
[oracle:/home/oracle/testdb/testdir#]cat OLC9_ACC_BONUS_3436.bad
BONUS_AMOUNT
)

通常原因是因為資料檔案中的格式有問題,需要檢查外部表呼叫的資料檔案格式是否正確.

[@more@]

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