External Tables :

最新推荐文章于 2021-04-04 04:23:14 发布

cuizhuizheng5676

最新推荐文章于 2021-04-04 04:23:14 发布

阅读量315

点赞数

文章标签：数据库

本文深入讲解Oracle外部表的创建和使用方法，包括元数据定义、数据读取限制及两种访问方式：oracle_loader和imp/exp。文章还展示了如何通过SQL语句创建外部表，并配置其属性如位置、访问类型和拒绝限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

它是只读的表，可以做为一个虚拟表。数据存在数据库外面的单一文件里。
外部表的元数据是在创建表的时候被定义的，存在其他的数据库或文件中。
Oracle 数据可以借助外部表把数据存在单一文件或从文件中卸载。
外部表的数据可以读但不能操作或建索引。

Oracle提供了两种访问外部表的方式：
1.oracle_loader:sqlldr，通过编写控制文件。
2.imp/exp:逻辑输入输出，数据库备份和修复数据文件时经常用到，可以在oaracle不同版本之间移植数据库的数

据。

创建外部表的时候：

在Create table 加上external_table_clause
指定Organization as External,表明数据来自外部数据库
External_table_clause:包括外部数据的属性，访问的类型，拒绝的limit
External_data_properties:default directory,access parameters,location

>实际上我们并非真的创建了一个表，而是创建了元数据在数据字典中，这样就可以访问外部数据
>Organization指定了表中行的存储顺序。
>Organization and External就把这个只读的表定位在外部数据库。
>Access driver是一种应用程序接口，它负责把外部的数据解释成database可以利用的数据，如果
没有指定，oracle server默认使用sqlloader
>Reject limit表明在查询终止和Oracle返回错误之前，可以允许在查询中有多少个变换错误。
>Default Directory指定external数据存在文件的位置，默认目录可以被用去存储错误的日志，多
个目录可以促进在多个磁盘上的load.
>Optional access parameters允许你指定访问的参数。Oracle不做任何事情，它解释外部数据中的信息。
>Location 指定外部数据源的位置，通常是一个文件。Oracle也不对它做任何解释。

SQL> grant create any directory to zhaoyu;

授权成功。
SQL> create or replace directory ocp_contact as 'D:ocp_contact';

目录已创建。

SQL> run
1 create table itpub(name varchar2(20),postbox varchar2(30),subject varchar2(10),
2 phone varchar2(16),address varchar2(20),overpass date,qq number(10),msn varchar2(20))
3 organization external
4 (type oracle_loader
5 default directory ocp_contact
6 access parameters
7 (records delimited by newline
8 badfile 'bad_ocp'
9 logfile 'log_ocp'
10 fields terminated by X'09'
11 (name char,postbox char,subject char,phone char,address char,
12 overpass date_format date mask "dd-mm-yyyy",qq number,msn char))
13 location('itpub ocp.txt'))
14 parallel 5
15* reject limit 200

表已创建。

SQL> desc itpub
名称是否为空? 类型
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
POSTBOX VARCHAR2(30)
SUBJECT VARCHAR2(10)
PHONE NUMBER(16)
ADDRESS VARCHAR2(20)
OVERPASS DATE
QQ NUMBER(10)
MSN VARCHAR2(20)

SQL> select * from itpub;
select * from itpub
*
第 1 行出现错误:
ORA-12801: 并行查询服务器 P000 中发出错误信号
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-30653: 已达到拒绝限制值
ORA-06512: 在 "SYS.ORACLE_LOADER", line 52
ORA-06512: 在 line 1

查看日志，找出错误原因，KUP-04021 field formatting error for field string

Cause: There was an error trying to find the contents of the specified field in a record.

Action: This message should be followed by another message containing more specific information

about the cause of the error.

KUP-04023 field start is after end of record

Cause: This can happen when specifying either absolute or relative positioning for a field.

Action: Verify that the definition of the layout of fields in the data file is correct. If it is

valid for fields at the end of the record to be missing, then use the MISSING FIELD VALUES ARE NULL

clause in the access parameters

主要是日期格式不对，还有就是存在space.

SQL> select rownum as rank,name,postbox,phone,address from
2 (select name,postbox,phone,address from itpub order by name)
3 where rownum<16;

RANK NAME POSTBOX PHONE ADDRESS
---------- -------------------- ------------------------------ ----------------

--------------------
1 remoteman zhaocl@gmail.com 山东东营
2 21ccz 21ccz@vip.sina.com 上海
3 74_yujun 74_yujun@163.com 北京
4 954ken ken954@sina.com 福州
5 99willicn 99willicn@sina.com 北京
6 9IAS 9ias@163.com 北京
7 AIXoracle weihyuan@263.net 兰州
8 CUTE_ORACLE zhenyuwang9999@163.com 杭州
9 Christen OlympicsInBeijing@hotmail.com 郑州
10 DavidHsu davidhsu_87@hotmail.com 台湾
11 Galahad.Wu wflower@hotmail.com 深圳

RANK NAME POSTBOX PHONE ADDRESS
---------- -------------------- ------------------------------ ----------------

--------------------
12 George LeeGeorge@sina.com 上海
13 George ligeorge@vip.sina.com 上海
14 Graeme graeme_wong@hotmail.com
15 HHP haipeng_hong@yahoo.com

已选择15行。

(已经去掉QQ，MSN，OVERPASS三个数据列）

Create index with create table statement:

SQL> create table new_emp
2 (empno number(6) primary key using index
3 (create index emp_id_idx on new_emp(empno)),
4 first_name varchar2(20),
5 last_name varchar2(25));

表已创建。

SQL> select index_name,table_name from user_indexes
2 where table_name='NEW_EMP';

INDEX_NAME TABLE_NAME
------------------------------ ----------------------------
EMP_ID_IDX NEW_EMP

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8382469/viewspace-259848/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8382469/viewspace-259848/