三 外部表
官方文档：Administrator’s Guide -> 15 Managing Tables -> Managing External Tables
Utilities->Part III External Tables –> 14 The ORACLE_DATAPUMP Access Driver
外部表：把数据保存在操作系统层面上，并不保存在表空间上，即可以把数据写入外部表又可以读取外部表到数据库(只读到内存里)。外部表就是操作系统上的一个二进制文件不是纯文本文件。可用strings查看
场合：导出的数据可用于二次开发。数据迁移
要求使用oracle数据泵导出外部表并跨库加载
过程：JCH111卸载数据，传输到JCH112加载数据
创建directory
sqlplus / as sysdba@JCH111
create user ext identified by ext;
grant dba to ext;
conn ext/ext
create directory dir_dmp as ‘/home/oracle‘;
grant read,write on directory dir_dmp to public; 授予读/写权限给目录对象，把目录对象给所有用户
SQL> sqlplus / as sysdba@JCH111
create user ext identified by ext;
grant dba to ext;
conn ext/ext
create directory dir_dmp as ‘/oracle/dmp‘;
SQL>
User created.
SQL>
Grant succeeded.
SQL> Connected.
SQL>
Directory created.
SQL> grant read,write on directory dir_dmp to public;
Grant succeeded.
SQL>
创建外部表，使用数据泵工具卸载数据，使用2个cup并行卸载，加快速度
create table t2
organization external
(type oracle_datapump
default directory dir_dmp
location (‘t2_part1.dat‘,‘t2_part2.dat‘)
)
parallel 2
as
select owner,object_id,object_name from dba_objects where owner=‘SYSTEM‘;
SQL> select count(*) from t2;
COUNT(*)
----------
447
SQL>
strings命令可以读取卸载得到的二进制文件内容
strings t2_part1.dat
在JCH112创建外部表t3读取t2_part1.dat和t2_part2.dat
scp t2_part1.dat t2_part2.dat ocm2:~/
看JCH112是否传送成功：
创建的外部表加载的数据，可以放到内存中，介质存放在外部表里，不是存放在数据文件中？
sqlplus / as sysdba@JCH112
create user ext identified by ext;
grant dba to ext;
conn ext/ext
create directory dir_dmp as ‘/oracle‘;
grant read,write on directory dir_dmp to public;
create table t3 (owner varchar2(100),object_id varchar2(100),object_name varchar2(100))
organization external
(type oracle_datapump
default directory dir_dmp
location (‘t2_part1.dat‘,‘t2_part2.dat‘));
select count(*) from t3;
查看生成的日志文件：
