第六课 3. 外部表

三 外部表

官方文档：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;

加载数据到t3

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;

查看生成的日志文件：