外表(external table)"/>
oracle外表(external table)
定义
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
- Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
- 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
- ...
更多推荐
oracle外表(external table)
发布评论