介绍oracle外部表

介绍oracle外部表

外部表（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 上面创建一个目录，或者选择一个已经存在的目录。

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

将以上数据填充进ms-excel 保存为students_info.csv

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

SQL> show user

USER is "SYS"

SQL> create or replace directory data_dir

  2  as 'c:\sqlloader\external';

Directory created.

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

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

Grant succeeded.

创建外部表

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

SQL> ed

Wrote file afiedt.buf

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

  2  id     varchar2(16),

  3  name   varchar2(30),

  4  email  varchar2(20),

  5  gender char(1),

  6  age    number(2)

  7  )

  8  organization external--指定为外部表。

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

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

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

 12    ( records delimited by newline

 13      fields terminated by ','

 14    )

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

 16* )

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: 操作在外部组织表上不受支持

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 的时候才是可用的。

SQL> desc emp

 Name                                      Null?    Type

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

 EMPLOYEE_ID                                        NUMBER(6)

 FIRST_NAME                                         VARCHAR2(20)

 LAST_NAME                                 NOT NULL VARCHAR2(25)

SQL> create table export_emp

  2  organization external

  3  ( type oracle_datapump

  4    default directory data_dir

  5    location ('export_emp.dmp')

  6  ) as select * from emp;

Table created.

现在我们把 emp 这张表中的数据导出到了data_dir 目录对象指定的位置，文件的格式是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.

SQL> create table import_emp

  2  ( employee_id number(6),

  3    first_name  varchar2(20),

  4    last_name   varchar2(25)

  5  )

  6  organization external

  7  ( type oracle_datapump

  8    default directory data_dir

  9    location ('export_emp.dmp')

 10  );

Table created.

SQL> select count(*) from import_emp;

  COUNT(*)

----------

       107

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

create index test_idx on import_emp(employee_id)

                         *

ERROR at line 1:

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

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

转载于:http://blog.itpub.net/26110315/viewspace-731506/