oracle外部表External table

外部表External table

把一个普通的文本格式的OS文件看作是Oracle数据库的外部表，Oracle可以象普通表一样进行select 操作，可以建视图，可以与其他进行连接等，但不能对其进行DML操作，即该表是只读的！(10g里可借此导出数据至平面dmp文件）。

External table和正规的表很相似，以下的几点需要注意：

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

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

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

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

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

假设如下的两个平面文件

1.dat：

7301,SMITH,CLERK,7902,17-DEC-80,100,0,20

7402,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30

7503,WARD,SALESMAN,7698,22-FEB-81,450,0,30

7504,JONES,MANAGER,7839,02-APR-81,1150,0,20

2.dat：

7611,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30

7612,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30

7913,MILLER,CLERK,7782,23-JAN-82,3500,0,10

创建一个逻辑目录：

CREATE DIRECTORY DIR_TEST AS 'D:TEMP';

定义外部表：

CREATE TABLE EXT1

emp_id number(4),

ename varchar2(12),

job varchar2(12) ,

mgr_id¡¡number(4) ,

hiredate date,

salary number(8),

comm number(8),

dept_id number(2))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY DIR_TEST

ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY ',')

LOCATION('1.DAT','2.DAT'))

查询外部表：

select * from ext1;

得到外部表的有关信息：

SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETER S

FROM DBA_EXTERNAL_TABLES;

select * from DBA_EXTERNAL_LOCATIONS;

================================================== =

升级Oracle中的外部表

--创建逻辑目录dirls,外部表数据文件所在位置

CREATE OR REPLACE directory DIR_x1 as 'd:dbexp';

--创建外部表结构create table x1

(c1 VARCHAR2(10)

, c2 VARCHAR2(10)

)

organization external

( type oracle_loader

default directory dir_x1

access parameters ( records delimited by newline

badfile 'x1.bad'

discardfile 's1.dis'

logfile 'x1.log'

fields terminated by ','

optionally enclosed by '"'

missing field values are null

)

location ('x1.txt','x2.txt')

)

/html/200509/2005090516013200010796.html

甲骨文（Oracle）数据库的外部表（external tables ），作为一个平台文件，在支撑Oracl e SQL的时候，对整合Oracle数据仓库以及“寄生”在服务器上的表数据的外部元数据起着至关重要的作用。

此外，外部表也可用来当作“csv”文件，允许这种类型的外部表数据在微软的Excel电子数据表中得以使用。

一个名为Peter Kok的技术人员，发布了一则Oracle外部表获得升级的技巧，他列出的操作步骤如下：

1、在外部表中定义一个“view”；

2、在这个“view”中，分别为“插入”、“更新”和“删除”定义一个“INSTEAD OF”型的triggers；

3、在这些triggers中，编写PL/SQL以执行必要的处理过程。

Kok接着出示了例子的代码，还提供了这项技术的免费下载，在最后也提出了一些重要的“警告”：

The problem is with doing a multiple delete (i.e. a delete statement that affec ts more than one row). As we noted before, the INSTEAD OF trigger is implici tly for each row. This means statement-level information is unavailable.

例子如下所示：

create or replace view emp_ext_tab_vw

as

select rownum rownumber

, emp.empno empno

, emp.ename ename

, emp.job job

, emp.mgr mgr

, emp.hiredate hiredate

, emp.sal sal

, m comm

, emp.deptno deptno

from emp_ext_tab emp

order by rownumber asc

/

create or replace trigger emp_ext_tab_vw_brd

instead of delete on emp_ext_tab_vw

begin

--

emp_ext_tab_dml.delete_record ( :OLD.rownumber );

--

end;

/