oracle外表(external table)

来源：互联网发布：三菱plc仿真软件序列号编辑：程序博客网时间：2024/04/25 13:41

定义

External tables access data in external sources as if it were in a table in the database.

You can connect to the database and create metadata for the external table using DDL.

The DDL for an external table consists of two parts: one part that describes the Oracle

column types, and another part (the access parameters) that describes the mapping of

the external data to the Oracle data columns.

u 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

u 数据在数据库的外部组织，是操作系统文件。

u 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。

u 数据是只读的。（外部表相当于一个只读的虚表）

u 不可以在上面运行任何 DML 操作，不可以创建索引。

u 可以查询操作和连接。可以并行操作。

建立外部表的步骤：

1、创建以“,”分隔的文件“TestTable.csv”至“D:\Test”

2、创建一个Directory：

create directory TestTable_diras 'D:\Test' ;

3、创建一个外部表：

create table TestTable(

ID varchar2 ( 10 ),

NAME varchar2 ( 20 ),

TYPE varchar2 ( 20 ),

AGEvarchar2 ( 20 ))

organization external (

type oracle_loader

default directory TestTable_dir

access parameters (fields terminatedby ',' )

location ( 'TestTable.csv' )

);

各类参数说明

1、type oracle_loader

数据转换驱动器，oracle_loader为默认，也可以改换其他

2、defaultdirectory TestTable_dir

location ('TestTable.csv')

指定外部表所在文件夹以及指定文件

3、accessparameters

设置转换参数，例如(fields terminatedby',')表示以','为字段间的分隔符

● 参数由访问驱动程序定义

外部表的错误处理

1、REJECT LIMIT子句

在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。

* 默认的REJECT LIMIT值为0

* REJECT LIMIT UNLIMITED则不会报错

2、BADFILE 和 NOBADFILE 子句

在accessparameters中加入BADFILE'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中

使用NOBADFILE子句则表示忽略转换错误的数据

● 如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件

● BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。

3、LOGFILE 和 NOLOGFILE 子句

在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中

使用NOLOGFILE子句则表示不记录错误信息到log中

● 如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件

修改外部表语句

外部表与堆表一样可以之用ALTER TABLE命令修改表属性

* REJECT LIMIT --错误数

* DEFAULT DIRECTORY --默认目录

* ACCESS PARAMETERS --参数

* LOCATION --数据文件

* ADD COLUMN --增加列

* MODIFY COLUMN --列定义

* DROP COLUMN --删除列

* RENAME TO --外部表更名

其他约束

● 外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。

● 外部表不能建立索引，如要建立，则需要先create table XX as select * from TestTable

PS:

1.外部表可以加载和卸载数据泵格式的数据，只需把organization external里的参数type设置为oracle_datapump。

            create table all_objects_unload
            organization external
                   (
                   type oracle_datapump
                  default directory testdir
                  location('allobjects.dat')
                  )
            as
            select * from all_objects

教会你掌握oracle外表(external table)

外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多。在这篇文章里，我们为大家演示了
三步就掌握oracle外表过程。通过这次学习，也许大家就会发展原来学习oracle也是好容易哦。

第一步：创建目录并授权。目录是数据文件的存放目标，数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。

sys@TEST>!ls /home/oracle/temp
user.ctl userlist.txt user.log
rudolf@TEST>
sys@TEST>conn system/alibaba
Connected.
sys@TEST>
sys@TEST>CREATE DIRECTORY TEMP AS '/home/oracle/temp/';
Directory created.
sys@TEST>grant read,write on directory TEMP to rudolf;
Grant succeeded.

复制代码

第二步：创建外表与测试

rudolf@TEST>CREATE TABLE "USERLIST"
2 (
3 ID NUMBER,
4 USERNAME VARCHAR2(30),
5 EMAIL VARCHAR2(128)
6 )
7 ORGANIZATION external
8 (
9 TYPE oracle_loader
10 DEFAULT DIRECTORY TEMP
11 ACCESS PARAMETERS
12 (
13 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
14 BADFILE 'TEMP':'userlist.bad'
15 DISCARDFILE 'TEMP':'userlist.dis'
16 LOGFILE 'TEMP':'user.log'
17 READSIZE 1048576
18 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
19 MISSING FIELD VALUES ARE NULL
20 REJECT ROWS WITH ALL NULL FIELDS
21 (
22 ID CHAR(30)
23 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
24 USERNAME CHAR(30)
25 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
26 EMAIL CHAR(128)
27 TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
28 )
29 )
30 location
31 (
32 'userlist.txt'
33 )
34 )REJECT LIMIT UNLIMITED
35
rudolf@TEST>/
Table created.
rudolf@TEST>l
1 select id,username from userlist where rownum < 10
2*
rudolf@TEST>/
ID USERNAME
---------- ------------------------------
1 RudolfLu
3 tomgu
6 coug
7 chao_ping
8 parrotao
9 cnoug
10 FilsDeDragon
11 Dragon
9 rows selected.

复制代码

瞧，成功了。外表就这么简单。可是只有二步啊，第三步在哪里呢？你也许会问。还有啊，userlist.txt要固定的格式吗？create table...的语法这样的狂复杂，每一项都是什么含义呢？

这就是第三步要教给大家的东西了。

第三步：理解外表数据结构与create table ... organization external语法。大家都用过sqlldr吧？外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了。那么语法呢？嘿嘿，别急，让我们先来做个sqlldr的练习吧：

[oracle@rac1 temp]$ head -10 userlist.txt
1,"RudolfLu"
3,"tomgu"
6,"coug"
7,"chao_ping"
8,"parrotao"
9,"cnoug"
10,"FilsDeDragon"
11,"Dragon"
15,"Xavier"
[oracle@rac1 temp]$ cat user.ctl
LOAD
INFILE '/home/oracle/temp/userlist.txt'
badfile '/home/oracle/temp/userlist.bad'
discardfile '/home/oracle/temp/userlist.dis'
APPEND
INTO TABLE userlist
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( id char(30),
username char(30)
)
rudolf@TEST>create table userlist
2 (id number,
3 username varchar2(30)
4 );
Table created.
rudolf@TEST>!
[oracle@rac1 temp]$ sqlldr rudolf/nix@test2.world control=./user.ctl external_table=GENERATE_ONLY

复制代码

注意，我们加了一个external_table的参数。它的作用是告诉sqlldr不用真实load数据，而是生成包含external table 创建脚本的log文件。

[oracle@rac1 temp]$ ls
user.ctl userlist.txt user.log
[oracle@rac1 temp]$ cat user.log
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Dec 10 20:50:19 2003
Control File: ./user.ctl
Data File: /home/oracle/temp/userlist.txt
Bad File: /home/oracle/temp/userlist.bad
Discard File: /home/oracle/temp/userlist.dis
...
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/temp/'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_USERLIST"
(
ID NUMBER,
USERNAME VARCHAR2(30)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.dis'
LOGFILE 'user.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
USERNAME CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' )
)
location
(
'userlist.txt'
)
)REJECT LIMIT UNLIMITED
...

0 0