--首先创建 scott.countries实体表，用于做实验
SQL> create table scott.countries(
2 country_id number,
3 country_name varchar2(30),
4 country_reigion varchar2(30)
5 );
Table created.
SQL> insert into scott.countries values(1,'AMERICA','america');
1 row created.
SQL> insert into scott.countries values(2,'china','zhuzhou');
1 row created.
SQL> insert into scott.countries values(3,'JAPAN','dongjing');
1 row created.
SQL> commit;
Commit complete.
--参考官方datapump创建外部表的案例（其数据来源于内部实体表），如下：
做以下修改后（红色字体部分内容），创建外部表
CREATE TABLE scott.countries_ext
ORGANIZATION EXTERNAL
(
TYPE Oracle_DATAPUMP
DEFAULT DIRECTORY dir --生成dmp文件存放路径
LOCATION ('countries_ext.dmp') --生成外部文件的名称
)
AS SELECT * FROM scott.countries; --数据来源于实体表
--执行以上创建外部表的语句
SQL> CREATE TABLE scott.countries_ext
2 ORGANIZATION EXTERNAL
3 (
4 TYPE ORACLE_DATAPUMP
5 DEFAULT DIRECTORY dir
6 LOCATION ('countries_ext.dmp')
7 )
8 AS SELECT * FROM scott.countries;
Table created.
SQL> select * from scott.countries_ext;
COUNTRY_ID COUNTRY_NAME COUNTRY_REIGION
---------- ------------------------------ ------------------------------
1 AMERICA america
2 china zhuzhou
3 JAPAN dongjing
由此可见，创建外部实体表成功。
--查看dir外部文件存放路径，并检查是否生成了countries_ext.dmp文件
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- --------------------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SYS DIR /home/oracle/dir
[oracle@gc1 dir]$ cd /home/oracle/dir
[oracle@gc1 dir]$ ls
COUNTRIES_EXT_14214.log countries_ext.dmp ldr.ctl ldr.log PROD_MY_12177.log PROD_MY_14213.log PROD_MY_14214.log prod_my.data
二、datapump创建外部表，数据来源于外部dmp文件
--gc2机器创建外部表存放文件目录，同gc1
[oracle@gc2 dbs]$ cd /home/oracle
[oracle@gc2 ~]$ mkdir dir
[oracle@gc2 ~]$ cd dir
--数据库创建对应文件目录，并赋予操作权限给所有用户
SQL> create directory dir as '/home/oracle/dir';
Directory created.
SQL> grant all on directory dir to public;
Grant succeeded.