前言
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復制粘貼就可以了。
