oracle外部表kup-04023,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@]