外部表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_PARAMETERS
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')
)

http://www.softhouse.com.cn/html/200509/2005090516013200010796.html

甲骨文（Oracle）数据库的外部表（external tables ），作为一个平台文件，在支撑Oracle 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 affects more than one row). As we noted before, the INSTEAD OF trigger is implicitly 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
, emp.comm 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;
/

10g里可借此导出数据至平面dmp文件：http://www.orafaq.com/node/848

create table x_1
organization external
( type oracle_datapump
default directory dir_dmp
location ('x_1.dmp')
) as
select owner,table_name,tablespace_name from dba_tables where owner='XSB';

select * from x_1;

create table x_2 (
owner varchar2(100),table_name varchar2(100),tablespace_name varchar2(100)
)
organization external
( type oracle_datapump
default directory dir_dmp
location ('x_1.dmp')
);

xsb 发表于:2005.08.19 09:21 ::分类: ( Oracle ) ::阅读:(771次) :: Permanent link :: 引用 (0)

re: 外部表External table [回复]

The following hypothetical SQL statements create an external table in the hr schema named admin_ext_employees and load its data into the hr.employees table.

CONNECT / AS SYSDBA;
-- Set up directories and grant access to hr
CREATE OR REPLACE DIRECTORY admin_dat_dir
AS '/flatfiles/data';
CREATE OR REPLACE DIRECTORY admin_log_dir
AS '/flatfiles/log';
CREATE OR REPLACE DIRECTORY admin_bad_dir
AS '/flatfiles/bad';
GRANT READ ON DIRECTORY admin_dat_dir TO hr;
GRANT WRITE ON DIRECTORY admin_log_dir TO hr;
GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
-- hr connects
CONNECT hr/hr
-- create the external table
CREATE TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile admin_bad_dir:'empxt%a_%p.bad'
logfile admin_log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask "dd-mon-yyyy",
salary, commission_pct, department_id, email
)
)
LOCATION ('empxt1.dat', 'empxt2.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;
-- enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;
-- load the data in hr employees table
INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
hire_date, salary, commission_pct, department_id, email)
SELECT * FROM admin_ext_employees;