oracle外部表位置,Oracle外部表

可以像对其他表一样，使用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_NAMEDIRECTORY_PATH

------------

------------------ ---------------------

SYSSCOTT_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;