oracle外部表

参考http://space.itpub.net/26110315/viewspace-731506

http://blog.youkuaiyun.com/robinson_0612/article/details/6078481

外部表（external tables）是对sql*loader 的一个补充，可以让我们访问数据库外部的数据资源。不过外部表存在着诸多的限制，所以我们使用前必须对其有充分的了解。外部表中的一些限制：

1、不能在外部表上执行 DML操作。

2、不能在外部表上创建 index。

3、不支持 analyze 分析语句，但是可以使用 dbms_stats。

4、 不支持虚拟列。

ref: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4347075367803

oracle 10g 以后可以允许我们把数据库中的表，write out 成为外部表。

外部表创建前的准备工作

在创建外部表前我们需要做一些准备工作。

1、在OS 上面创建一个目录，或者选择一个已经存在的目录。

mkdir /home/oracle/dump_dir

2、确保上面创建的目录安装oracle 数据库的用户有读写的权利(这一点在类Unix 系统中需要注意)。

3、把外部表的数据实际数据放置在这个目录下。

930070350,smith,1111@qq.com,m,23

930070351,tom,2222@qq.com,m,27

930070352,peter,3333@qq.com,m,24

930070353,jack,4444@qq.com,m,25

930070354,king,5555@qq.com,m,26

将以上数据填充进日记本 保存为 aa.txt

mv aa.txt /home/oracle/dump_dir

4、在数据库系统中创建目录对象（默认情况下拥有DBA权限的用户才能创建目录对象）。

SQL> show user

USER is "SYS"

SQL> create or replace directory data_dir as '/home/oracle/dump_dir ';

Directory created.

5、把目录对象的read,write 权限授予给需要使用该目录对象的用户。

SQL> grant read,write on directory data_dir to scott;

Grant succeeded.

一、oracle_loader方式创建外部表

做完了上述的准备工作，我们就可以来创建外部表了。

SQL> ed

Wrote file afiedt.buf

create table students(-- 外部表的创建语句和通常的create table 语句大部分相同。

    id varchar2(16),

    name varchar2(30),

    email varchar2(20),

    gender char(1),

    age number(2)

)

  organization external--指定为外部表。

 ( type oracle_loader-- 指定外部表的加载类型。有oracle_loader,oracle_datapump。

     default directory data_dir--指定外部表默认的读写位置，是通过目录对象指定的，而不是实际的目录。

     access parameters--指定根据什么规则把外部文件中的数据加载到外部表中。

     ( records delimited by newline

         fields terminated by ','

     )

 location ('aa.txt')--指定外部表加载的数据源的位置。

 )

SQL> /

Table created.

ref： http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm#i1012231

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#ADMIN01507

在创建外部表的过程中，并不会实际的加载外部数据源中的数据，也不会验证access parameter 参数的有效性，这些动作要等到向外部表发出查询的时候才执行。

SQL> select * from students;

ID NAME EMAIL GEND AGE

---------- ---------- ------------ ---- ----------

930070350 smith 1111@qq.com m 23

930070351 tom 2222@qq.com m 27

930070352 peter 3333@qq.com m 24

930070353 jack 4444@qq.com m 25

930070354 king 5555@qq.com m 26

使用oracle_loader 方式加载外部数据的时候，根据不同的情况像sql*loader 中一样也会生成 log,bad,discard file.当加载出现错误的时候，查看log文件非常重要。

现在我们可以访问students 中的数据，但是 不能在students 表中执行DML操作和创建索引，这限制了students 表的用处，把students 表中的数据再加载到普通的oracle table 中来解决这个问题。

SQL> create index students_idx on students(id);

create index students_idx on students(id)

*

ERROR at line 1:

ORA-30657: 操作在外部组织表上不受支持

根据外部表students创建oracle内部表
create table  students_info as select * from students;

SQL> select * from students_info;

ID NAME EMAIL GEND AGE

---------- ---------- ------------ ---- ----------

930070350 smith 1111@qq.com m 23

930070351 tom 2222@qq.com m 27

930070352 peter 3333@qq.com m 24

930070353 jack 4444@qq.com m 25

930070354 king 5555@qq.com m 26

SQL> create index students_info_idx on students_info(id);

Index created.

也就是说 可以借助外部表把oracle 数据库外的数据加载进oracle 数据库中的普通表中。

以上介绍的都是使用 oracle_loader 的方式来加载数据库外的数据，下面我们考虑使用一种新的方式来加载和卸载数据。

注： 卸载数据只有使用oracle_datapump 的时候才是可用的。

二、 oracle_datapump 方式卸载数据
SQL> desc dept

Name Null? Type

----------------------------------------- -------- -----------------------

DEPTNO  NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

 create table export_dept
 organization external
 ( type oracle_datapump
 default directory data_dir
 location (' export_dept.dmp')
 )
parallel 2
as select * from dept;

Table created.

现在我们 把 emp 这张表中的数据导出到了data_dir 目录对象指定的位置，文件的格式是oracle_datapump 专用的格式。

注： oracle_datapump 加载和卸载数据的格式都是oracle_datapump 专用的。

三、oracle_datapump方式加载数据
现在我们就可以 把oracle_datapump 方式生成的 export_emp.dmp 文件传输到其他系统中了，可以用作 数据迁移的一种方式。下面演示把刚才导出的文件加载到其他schema 下。

SQL> show user

USER is "SYS"

SQL> grant read,write on directory data_dir to test;

Grant succeeded.

SQL> conn test/test

Connected.

 
create table import_dept
 (

DEPTNO  NOT NULL NUMBER(2),

DNAME VARCHAR2(14),

LOC VARCHAR2(13)

)
 organization external
 ( type oracle_datapump
 default directory data_dir
 location (' export_dept.dmp')
 );

Table created.

SQL> select count(*) from import_dept;

COUNT(*)

----------

107

SQL> create index test_idx on import_dept(deptno);--记得import_emp 是个外部表哦。

create index test_idx on import_dept(deptno)

*

ERROR at line 1:

ORA-30657: 操作在外部组织表上不受支持

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29302187/viewspace-1972812/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29302187/viewspace-1972812/