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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23850820/viewspace-1048515/