外部表筆記-datapump

datapump型別的外部表

datapump 相對於oracle loader的優勢在於即可以載入資料，又可以解除安裝資料。

示例一
CREATE TABLE dept_xt (dept_no INT, dept_name CHAR(20), location CHAR
ORGANIZATION EXTERNAL (TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY load_d
ACCESS PARAMETERS (LOGFILE log_dir:deptxt) LOCATION ('dept_dmp'));

示例二
將資料解除安裝到指定目錄下
CREATE TABLE inventories_xt
2 ORGANIZATION EXTERNAL
3 (
4 TYPE ORACLE_DATAPUMP
5 DEFAULT DIRECTORY def_dir1
6 LOCATION ('inv_xt.dmp')
7 )
8 AS SELECT * FROM inventories;
Table created.

[@more@]

將上一步解除安裝的資料檔案，做為源建立一個新的外部表
CREATE TABLE inventories_xt2
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_xt.dmp')
);

為了提高解除安裝速度，並行建立
CREATE TABLE inventories_xt3
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_xt1.dmp', 'inv_xt2.dmp', 'inv_xt3.dmp')
)
PARALLEL 3
AS SELECT * FROM inventories;

使用多檔案做為源的示例
先匯出一部分資料
CREATE TABLE inv_part_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_p1_xt.dmp')
)
AS SELECT * FROM inventories WHERE warehouse_id < 5;

再匯出第二部分資料
drop table inv_part_xt;
CREATE TABLE inv_part_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_p1_xt.dmp')
)
AS SELECT * FROM inventories WHERE warehouse_id >= 5;

建立外部表，讀上面匯出的兩部分檔案
CREATE TABLE inv_part_all_xt
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_p1_xt.dmp','inv_p2_xt.dmp')
);

Table created.

比較兩個表的差異
SELECT * FROM inventories MINUS SELECT * FROM inv_part_all_xt;

解除安裝和載入BFILE的示例

解除安裝LONG的示例