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




