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.

将上一步卸载的数据文件，做为源创建一个新的外部表
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的示例