基于oracle_datapump的外部表实现过程

ygamodugky

　　一、创建外部表以及产生dmp文件
1、创建directory，需要有 create any directory权限：
CREATE DIRECTORY admin AS '/oracle/admin';
　　2、创建外部表：
SQL> CREATE TABLE emp_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY admin
LOCATION ('emp_xt.dmp')
)
AS SELECT * FROM emp;
　　Table created.
　　3、验证表的正确性：
SQL> select * from emp minus select * from emp_xt;
no rows selected
　　二、使用已产生的pump文件创建外部表
1、把产生的dmp文件复制到其它主机上
　　2、创建directory，需要有 create any directory权限：
SQL> create directory ext_dir as '/oracle/ext_dir';
　　3、创建外部表：
SQL> CREATE TABLE emp_xt2
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_dir
LOCATION ('emp_xt.dmp')
);
　　三、如果基表发生改变，如何更新数据源：
1、先删除原数据库的外部表以及创建外部表产生的dmp文件。
2、重新创建外部表。
3、把新产生的dmp文件复制到远程主机上即可。
　　四、Create an external table with three dump files and with a degree of parallelism of three.
1、创建三个dump文件的外部表：
SQL> CREATE TABLE emp_xt3
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY admin
LOCATION ('emp_xt1.dmp', 'emp_xt2.dmp', 'emp_xt3.dmp')
)
PARALLEL 3
AS SELECT * FROM emp;
　　2、Compare the data unload against emp. There should be no differences.
SQL> SELECT * FROM emp MINUS SELECT * FROM emp_xt3;
　　五、Create an external table containing some rows from table emp.
1、创建包含有部分数据的外部表，主要目的产生dmp文件：
SQL> CREATE TABLE emp_part_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY admin
LOCATION ('emp_p1_xt.dmp')
)
AS SELECT * FROM emp WHERE empno < 7654;

2、Create another external table containing the rest of the rows from emp.
SQL> drop table emp_part_xt;

Table dropped.

SQL> CREATE TABLE emp_part_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY admin
LOCATION ('emp_p2_xt.dmp')
)
AS SELECT * FROM emp WHERE empno >= 7654;

3、Create an external table that uses the two dump files created in Steps 1 and 2.
　　SQL> CREATE TABLE emp_part_all_xt
(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY admin
LOCATION ('emp_p1_xt.dmp','emp_p2_xt.dmp')
);
　　4、比较两张表：
SQL> select * from emp minus select * from emp_part_all_xt;
　　no rows selected