介紹oracle外部表

外部表（external tables）是對sql*loader 的一個補充，可以讓我們訪問資料庫外部的資料資源。不過外部表存在著諸多的限制，所以我們使用前必須對其有充分的瞭解。外部表中的一些限制：

1、不能在外部表上執行DML操作。

2、不能在外部表上建立index。

3、不支援analyze 分析語句，但是可以使用dbms_stats。

4、不支援虛擬列。

ref:

oracle 10g 以後可以允許我們把資料庫中的表，write out 成為外部表。

外部表建立前的準備工作

在建立外部表前我們需要做一些準備工作。

1、在OS 上面建立一個目錄，或者選擇一個已經存在的目錄。

2、確保上面建立的目錄安裝oracle 資料庫的使用者有讀寫的權利(這一點在類Unix 系統中需要注意)。

3、把外部表的資料實際資料放置在這個目錄下。

930070350,smith,1111@qq.com,m,23

930070351,tom,2222@qq.com,m,27

930070352,peter,3333@qq.com,m,24

930070353,jack,4444@qq.com,m,25

930070354,king,5555@qq.com,m,26

將以上資料填充進ms-excel 儲存為students_info.csv

4、在資料庫系統中建立目錄物件（預設情況下擁有DBA許可權的使用者才能建立目錄物件）。

SQL> show user

USER is "SYS"

SQL> create or replace directory data_dir

2 as 'c:\sqlloader\external';

Directory created.

5、把目錄物件的read,write 許可權授予給需要使用該目錄物件的使用者。

SQL> grant read,write on directory data_dir to hr;

Grant succeeded.

建立外部表

做完了上述的準備工作，我們就可以來建立外部表了。

SQL> ed

Wrote file afiedt.buf

1 create table students(-- 外部表的建立語句和通常的create table 語句大部分相同。

2 id varchar2(16),

3 name varchar2(30),

4 email varchar2(20),

5 gender char(1),

6 age number(2)

7 )

8 organization external--指定為外部表。

9 ( type oracle_loader-- 指定外部表的載入型別。有oracle_loader,oracle_datapump。

10 default directory data_dir--指定外部表預設的讀寫位置，是透過目錄物件指定的，而不是實際的目錄。

11 access parameters--指定根據什麼規則把外部檔案中的資料載入到外部表中。

12 ( records delimited by newline

13 fields terminated by ','

14 )

15 location ('students_info.csv')--指定外部表載入的資料來源的位置。

16* )

SQL> /

Table created.

ref：

在建立外部表的過程中，並不會實際的載入外部資料來源中的資料，也不會驗證access parameter 引數的有效性，這些動作要等到向外部表發出查詢的時候才執行。

SQL> select * from students;

ID NAME EMAIL GEND AGE

---------- ---------- ------------ ---- ----------

930070350 smith 1111@qq.com m 23

930070351 tom 2222@qq.com m 27

930070352 peter 3333@qq.com m 24

930070353 jack 4444@qq.com m 25

930070354 king 5555@qq.com m 26

使用oracle_loader 方式載入外部資料的時候，根據不同的情況像sql*loader 中一樣也會生成 log,bad,discard file.當載入出現錯誤的時候，檢視log檔案非常重要。

現在我們可以訪問students 中的資料，但是不能在students 表中執行DML操作和建立索引，這限制了students 表的用處，把students 表中的資料再載入到普通的oracle table 中來解決這個問題。

SQL> create index students_idx on students(id);

create index students_idx on students(id)

*

ERROR at line 1:

ORA-30657: 操作在外部組織表上不受支援

SQL> select * from students_info;

ID NAME EMAIL GEND AGE

---------- ---------- ------------ ---- ----------

930070350 smith 1111@qq.com m 23

930070351 tom 2222@qq.com m 27

930070352 peter 3333@qq.com m 24

930070353 jack 4444@qq.com m 25

930070354 king 5555@qq.com m 26

SQL> create index students_info_idx on students_info(id);

Index created.

也就是說可以藉助外部表把oracle 資料庫外的資料載入進oracle 資料庫中的普通表中。

以上介紹的都是使用oracle_loader 的方式來載入資料庫外的資料，下面我們考慮使用一種新的方式來載入和解除安裝資料。

注：解除安裝資料只有使用oracle_datapump 的時候才是可用的。

SQL> desc emp

Name Null? Type

----------------------------------------- -------- -----------------------

EMPLOYEE_ID NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

SQL> create table export_emp

2 organization external

3 ( type oracle_datapump

4 default directory data_dir

5 location ('export_emp.dmp')

6 ) as select * from emp;

Table created.

現在我們把 emp 這張表中的資料匯出到了data_dir 目錄物件指定的位置，檔案的格式是oracle_datapump 專用的格式。

注：oracle_datapump 載入和解除安裝資料的格式都是oracle_datapump 專用的。

現在我們就可以把oracle_datapump 方式生成的 export_emp.dmp 檔案傳輸到其他系統中了，可以用作資料遷移的一種方式。下面演示把剛才匯出的檔案載入到其他schema 下。

SQL> show user

USER is "SYS"

SQL> grant read,write on directory data_dir to test;

Grant succeeded.

SQL> conn test/test

Connected.

SQL> create table import_emp

2 ( employee_id number(6),

3 first_name varchar2(20),

4 last_name varchar2(25)

5 )

6 organization external

7 ( type oracle_datapump

8 default directory data_dir

9 location ('export_emp.dmp')

10 );

Table created.

SQL> select count(*) from import_emp;

COUNT(*)

----------

107

SQL> create index test_idx on import_emp(employee_id);--記得import_emp 是個外部表哦。

create index test_idx on import_emp(employee_id)

*

ERROR at line 1:

ORA-30657: 操作在外部組織表上不受支援

介紹oracle外部表

相關文章