可以像对其他表一样,使用SELECT语句查询外部表,但不能对外部表执行DML操作。这是因为它不是以段的形式存在于数据库中,而是以数据字典构造存在,指向外部的操作系统文件。外部表的操作系统文件通过Oracle目录对象定位。
1、目录对象
Oracle目录是指向物理路径的数据库对象。任何用户要创建目录,必须获得CREATE DIRECTORY权限。目录创建后,必须授予使用目录的Oracle数据库用户读写目录的权限。创建目录时,Oracle不会验证目录路径是否真的存在。目录不是模式对象,不管谁创建的,都为SYS所有,因此普通用户尽管可以创建目录,甚至可以授权目录给其他用户访问,但却无权删除目录。要查看有关目录的信息,可以查询DBA_DIRECTORIES或ALL_DIRECTORIES视图。
以下一组语句授权SCOTT用户创建目录,并由SCOTT用户授权给其他用户使用,由SYS负责删除
conn / as sysdba
已连接。
grant create any directory to scott;
授权成功。
conn scott/tiger
已连接。
create or replace directory scott_dir as 'd:\scott';
目录已创建。
grant read, write on directory scott_dir to public;
授权成功。
select * from all_directories where directory_name='SCOTT_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ ------------------ ---------------------
SYS SCOTT_DIR d:\scott
drop directory scott_dir;
ORA-01031: 权限不足
conn / as sysdba
已连接。
drop directory scott_dir;
目录已删除。
2、创建和使用外部表
外部表不再需要使用SQL*Loader将表读入数据库,这在一些馈送系统中会节省大量开支。要创建外部表,使用CREATE TABLE命令以及ORGANIZATION EXTERNAL关键字,这告诉Oracle表不以段的形式存在,此后指定操作系统文件的位置和布局。而这个指定可以通过SQL*Loader命令行自动生成。以下例子说明了外部表的创建方法。
1)用rmes用户登录mes数据库创建本例使用的表
create table r_lln_sn_t(id number, sn varchar2(100));
表已创建。
2)创建外部数据文件sn.txt,包含内容如下
1,215# 3QD 500 025 A ##654272491#010816 *215 5CTHN000014*=
2,215# 3QD 500 025 A ##654272491#010816 *215 5CTHN000025*=
3,215# 3QD 500 025 A ##654272491#010816 *215 5CTHN000036*=
4,215# 3QD 500 025 A ##654272491#010816 *215 5CTHN000047*=
5,215# 3QD 500 025 A ##654272491#010816 *215 5CTHN000058*=
3)创建控制文件sn.ctl,设置如下
load data
infile 'sn.txt'
badfile 'sn.bad'
truncate
into table r_lln_sn_t
fields terminated by ','
trailing nullcols
(id,sn)
此控制文件将在执行插入操作前截断目标文件。
4)用SQL*Loader生成创建外部表的语句,即是在sqlldr命令行指定external_table子句
sqlldr rmes/rmes control=sn.ctl external_table=generate_only
这将在日志文件sn.log中生成一条CREATE TABLE语句
CREATE TABLE "SYS_SQLLDR_X_EXT_R_LLN_SN_T"
(
"ID" NUMBER,
"SN" VARCHAR2(100)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'sn.bad'
LOGFILE 'sn.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"SN" CHAR(255)
TERMINATED BY ","
)
)
location
(
'sn.txt'
)
)REJECT LIMIT UNLIMITED;
5)将其中加粗标注的表名更换为自己想要的名称,将目录更改为自己创建的目录,并保证sn.txt文件在指定的目录中:
CREATE TABLE "X_R_LLN_SN_T"
(
"ID" NUMBER,
"SN" VARCHAR2(100)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'MY_DIR':'sn.bad'
LOGFILE 'sn.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY ",",
"SN" CHAR(255)
TERMINATED BY ","
)
)
location
(
'sn.txt'
)
)REJECT LIMIT UNLIMITED;
6)创建好目录
create directory my_dir as 'd:\';
目录已创建。
7)运行步骤5创建的建表语句创建外部表。
8)使用几条SELECT语句查询外部表,并尝试DML操作,可以看到,系统为每个SELECT生成了日志记录,日志文件名形如sn.log_xt,并且不允许执行DML操作。
3、使用数据泵引擎创建外部表
另一种创建外部表的方法是使用数据泵引擎,将数据库中正常的表的数据存储到操作系统的二进制文件中,二进制文件与通过expdp导出的文件类似。可以将二进制文件转移到其它数据库所在的操作系统平台上,然后在其数据库中创建和读取外部表。这个方法也给不同平台之间数据库表数据的迁移提供了一种方法。
如下基于当前数据库中的表创建一个外部表,通过数据泵引擎生成dmp文件,如果要生成的dmp文件不止一个,则应当指定使用parallel
create table rmes.r_recent_code_ext(recent_id, code_id, value1, value2, value3, value4, value5, recent_no, code_date, plant_id)
organization external
(
type oracle_datapump
default directory my_dir
location('recent_code_1.dmp', 'recent_code_2.dmp')
)
parallel
as select * from rmes.r_recent_code_t;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28974745/viewspace-2145725/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28974745/viewspace-2145725/