Oracle有多种方式从外部加载数据到数据库，比如 SQL Loader、数据泵工具(IMPDP)和外部表。本篇文章主要介绍外部表的使用。
外部表是 Oracle Database 9i 版本引入的一个功能，它使数据库可以访问系统文件，就像读取数据库里的普通表一样方便，也可以用SQL语句来操作外部表，但外部表是只读的，数据文件一定要存放在数据库服务器上并且依赖Oracle的目录对象来加载外部文件中的数据。
在数据库创建的外部表，并没有将实际数据放入表空间数据块中，而只是在数据字典里添加了外部表的元数据信息，Oracle需要访问驱动程序来读取外部文件中的数据，Oracle提供两种访问驱动，默认使用 ORACLE_LOADER。外部表访问驱动的语法与 SQL Loader控制文件的语法很相似。
我们以一个 csv (点击下载)数据文件为例，来演示如何创建外部表。
1.创建目录对象
在Oracle中需要通过创建指向外部数据文件所在路径的 目录对象 来访问服务器上存放的文件。比如测试的csv文件 extable_test.csv 存放的位置为：C:\app\Axl\oradata\extable\datafile ，则先创建指向这个位置的目录对象。
--创建目录，注意用户需要有创建"目录对象"的权限，如果没有，需要用管理员账户先赋权 GRANT CREATE ANY DIRECTORY TO 用户;
CREATE DIRECTORY EXTABLE_DIR_DATA as 'C:\app\Axl\oradata\extable\datafile'; --存放数据文件的目录对象
CREATE DIRECTORY EXTABLE_DIR_LOG as 'C:\app\Axl\oradata\extable\logfile'; --存放日志文件的目录对象
CREATE DIRECTORY EXTABLE_DIR_BAD as 'C:\app\Axl\oradata\extable\badfile'; --存放错误文件的目录对象
--上面创建了三个目录对象，分别用于存放数据文件、日志文件和未成功导入的错误文件，创建完成可以查询目录对象，如图1所示
SELECT * FROM DBA_DIRECTORIES;
--如要删除目录可以使用 DROP DIRECTORY EXTABLE_DIR_DATA;
--查询用户是否有相关目录的读写权限，如果没有，需要先赋予权限 GRANT READ,WRITE ON DIRECTORY EXTABLE_DIR_DATA TO 用户;
SELECT * FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('EXTABLE_DIR_DATA','EXTABLE_DIR_LOG','EXTABLE_DIR_BAD');图1:创建的目录列表
2.创建外部表
目录创建完成后，使用 CREATE TABLE ORGANIZATION EXTERNAL 来创建外部表
--创建外部表
CREATE TABLE EXTABLE_TEST(
cus_no varchar2(20),
age int,
sex varchar2(1),
aum decimal(10,2),
deposit decimal(10,2),
finance decimal(10,2),
fund decimal(10,2),
cuslevel varchar2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTABLE_DIR_DATA
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
CHARACTERSET ZHS16GBK
READSIZE 1048576
SKIP 1
BADFILE EXTABLE_DIR_BAD:'EXTABLE_TEST1.bad'
LOGFILE EXTABLE_DIR_LOG:'EXTABLE_TEST1.log'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(cus_no
,age
,sex
,aum
,deposit
,finance
,fund
,cuslevel
)
)
LOCATION ('extable_test.csv')
)
REJECT LIMIT UNLIMITED;外部表命令更多注解：
- CREATE TABLE：创建的表的字段和数据类型要与外部数据文件一致
- ORGANIZATION EXTERNAL：EXTERNAL表明创建的是外部表，还有其他两种组织类型，HEAP表示正常表；INDEX表示IOT
- TYPE ORACLE_LOADER：ORACLE_LOADER作为访问外部文件的驱动，Oracle提供两种访问驱动，默认的是ORACLE_LOADER，另外一种是ORACLE_DATAPUMP数据泵格式
- DEFAULT DIRECTORY：外部数据文件所在位置的目录对象
- ACCESS PARAMETERS：设置一些参数，告诉数据库如何处理外部表文件，注意在参数里面最好不要加其他内容，注释也不行，否则可能会报错
- RECORDS：表示记录以换行符结束
- CHARACTERSET：指定字符集，默认使用缺省字符集，如果加载的数据属于不同字符集，在这指定
- READSIZE：Oracle读取输入数据文件所用的默认缓冲区。这里设置是1MB。如果采用专用服务器模式，则这个内存来自 PGA，如果采用共享服务器模式，则来自 SGA。它用于缓存输入数据文件中对应一个会话的信息，如果使用的是共享服务器，内存从 SGA分配。
- SKIP 1：忽略数据文件中的前1行，此处是跳过csv中的字段标题行
- BADFILE：指定错误文件目录及文件名
- LOGFILE：字段日志文件目录及文件名
- FIELDS TERMINATED BY：指定分隔符，这里指定逗号作为字段间的分隔符
- OPTIONALLY ENCLOSED BY：指定封闭符，包含在此符号里的内容不会被分隔符分割，比如分隔符是逗号 ‘,’ 封闭符是 ‘"’ 双引号，如果外部表中某行数据是：abc,de",fg"h ,这行数据被读取后被分成两个字段，即abc和de",fg"h，在封闭符里分隔符将失效
- LRTRIM：删除首尾空白字符
- MISSING FIELD VALUES ARE NULL：字段值为空时将其值设为null
- REJECT ROWS WITH ALL NULL FIELDS：字段全部为空的行将会跳并且记录到bad file文件里
- ACCESS PARAMETERS的最后指定要加载的字段列表，这里可以单独设置字段外部文件字段的类型、TERMINATED的分割方式、封闭符、日期字段设置等
- LOCATION：指定外部表对应的数据文件名称，如果有多个文件需要导入，则以逗号隔开，比如 LOCATION (‘extable_test.csv’,extable_test2.csv’)
- REJECT LIMIT UNLIMITED：允许错误的发生个数，默认值为零。UNLIMITED表示错误数量不受限制
3.查看外部表
--查询外部表数据字典，创建的外部表如图2所示
SELECT * FROM DBA_EXTERNAL_TABLES;
--查询外部表平面文件数据字典，可以看到外部表对应访问的数据文件
SELECT * FROM DBA_EXTERNAL_LOCATIONS图2:创建的外部表
--查看外部表数据
SELECT * FROM EXTABLE_TEST;
--如果外部表数据加载失败，可以通过日志文件和错误文件来查看详细错误信息可以在外部表的日志目录对象的路径下看到生成了日志文件 EXTABLE_TEST.log
在数据库里创建的外部表是只读的，如果对其进行DML操作将会抛出异常，但可以将外部表的数据插入到标准数据表中，然后就可以实现和普通表一样的增删改查等操作。
4.外部表加载不同名的文件
我们上面创建的外部表加载的文件是 extable_test.csv ，如果后面要加载的文件名变化了，可以通过重新指示外部表的名称来加载
--要加载的文件变成了 extable_test2.csv ，通过LOCATION修改外部表名称，注意文件所在路径不能改变
ALTER TABLE EXTABLE_TEST LOCATION ('extable_test2.csv')这样修改后，下次再加载的文件就变成了 extable_test2.csv
