五、外部表

外部表是数据仓库的一个重要考点，它有两种方式，oracle_loader和oracle_datapump，oracle_loader使用flat file数据作为数据源，而datapump则使用xml方式作为数据源。外部表会涉及到directory这个知识点，directory需要注意的是用户的权限问题。

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

外部表的前面部分语法和堆表相同，但后面部分的语法则差异很大，特别是oracle_loader方式的access parameters参数，这参数里面的选项非常多，主要用于指示数据文件的格式以及对错误行的处理方法等。有兴趣可以参考sql_loader的Utilities文档，说的很细，看着都觉得烦，所以建议忽略，只需要关注字段格式和时间格式就足够了。

datapump部分就比较简单了，access parameters不需要，也可以使用create table as select from table来创建外部文件，外部文件以xml格式存储。

create table read_write_ext1

organization external

(

type oracle_datapump

default directory dir_data

location('pump.dat')

)

reject limit unlimited

as

select * from read_ext;