前言
emmm，外部表嘛，顾名思义，就是在数据库外面的表，也就是存在操作系统的文件，格式有txt，csv等等，我们能用数据库语句去读取它，但是无法像正常的表一样给它加索引，或者执行删除及更新操作。
下面我们通过几个简单的实验，来感受下外部表是如何工作的。
PS：创建语句挺难记的，如果数据较少，我觉得还不如打开PLSQL，直接复制粘贴进去。
实验
part one 在数据库创建访问txt格式文件的外部表
1、创建路径
操作系统的文件目录，需要在数据库里体现并定义出来，比如我们定义D:\external_dir这个目录为external_dir，创建脚本
create directory external_dir as 'd:\external_dir';
注：我们可以通过dba_directories和all_directories两个视图查看数据库都定义了哪些路径
2、外部表利用哪个用户访问，我们需要将外部表所在的路径的访问权授予该用户，比方说scott，创建脚本
grant read,write on directory EXTERNAL_DIR to scott;
3、我们在定义的路径里新建一个aa.txt文件，并写入如下数据
4、数据库层面登录到scott用户下，并创建外部表
CREATE TABLE DEPT_2 #创建的外部表表名
(
deptno NUMBER(2),
dname VARCHAR2(14), #定义字段类型
loc VARCHAR2(13)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER #类似引擎，如果目标文件是文本用ORACLE_LOADERER，如果是二进制文件，则用ORACLE_DATAPUMP
DEFAULT DIRECTORY external_dir #选择之前数据库定义的路径
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY "," #表示数据文件里的数据用逗号隔开
)
LOCATION('aa.txt') #文件名称
);
**ps:键入脚本的时候，标点符号要注意，使用英文的。**
5、创建成功，我们就可以使用select语句，查看文件数据了，但是无法执行删除或者更新语句，如果要把它变为本地表，可以使用子查询
create table tablename as select * from dept_2;
ps: 我们可以通过dba_external_tables&user_external_tables 视图查看数据库的外部表信息
part two 在数据库创建访问csv格式文件的外部表
前面的步骤和part one的一样，所以这里就不再赘述，唯一不同的是创建外部表脚本，脚本如下
CREATE TABLE DEPT_2
(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY external_dir
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ","
)
LOCATION('aaa.csv') #指定要加载的文件名称
)
REJECT LIMIT UNLIMITED; #这句得加，不然会报ORA-29913，ORA-30653错误
part three 利用外部表功能将数据库表转化为二进制文件
外部表也能当数据迁移工具使用，将数据库的表转化为操作系统里的二进制文件，然后在别的数据库里重新加载为外部表。
1、同样需要在数据库里定义一个路径
create directory pumb_dir as 'D:\pumb_dir';
2、授权给scott用户
grant read,write on directory pumb_dir to scott;
3、这里将dept表转化为二进制文件
CREATE TABLE DEPT_4
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP #二进制文件使用oracle_datapump
DEFAULT DIRECTORY pumb_dir
LOCATION('dept.dmp') #文件名称
)
AS
SELECT * FROM dept; 将表dept的内容转化成二进制文件
4、执行成功，我们就得到了dept.dmp文件，如何在另一个数据库里加载该文件，我们在part four里讲
part four 在数据库创建访问二进制格式文件的外部表
延续part three的实验，我们将实验得到的dept.dmp文件，在另一个数据库加载并打开
1、创建路径，并将上述的dept.dmp文件放入该目录
create directory pumb_dir as 'D:\pumb_dir';
2、授权
grant read,write on directory pumb_dir to scott;
3、在数据库里创建外部表
CREATE TABLE DEPT_6 #表名
(
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_DATAPUMP #二进制文件使用oracle_datapump
DEFAULT DIRECTORY pumb_dir
LOCATION('DEPT.DMP') #文件名称
);
4、此时我们就可以正常访问该外部表的数据了
后话
外部表的使用原理还是相对简单的，但是执行效率有待进一步确认，使用场景也有待考察，数据量较少的话，直接用PLSQL复制粘贴就可以了。
