外部表的使用

常用的表形式包括：普通表、分区表、嵌套表、临时表、簇表、外部表等。

不同于其他表形式，外部表是存储在操作系统的文件中的，而不是在数据库中。

下面实验一下外部表的使用方法：

一、外部表创建

1.创建directory：

SQL> conn scott/tiger@admin
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott

SQL> show user;
User is "scott"

SQL> CREATE DIRECTORY admin AS 'e:\oracle';

Directory created

2、创建外部表：
SQL>  CREATE  TABLE empext
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY admin
      LOCATION ('empext.dmp')
    )
    AS SELECT * FROM emp;

执行后在e:\oracle目录下产生文件EMP_EXT.DMP和EMP_EXT_1660_5544.log

3、查看表内容：
SQL> select * from emp_ext;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2964.50               10
 7839 KING       PRESIDENT       1981-11-17    6050.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1573.00               10

12 rows selected

执行时，在e:\oracle目录下又产生了文件EMP_EXT_1660_2204.log

日志文件的内容为“ LOG file opened at 09/26/12 13:39:52”

可以推测，每次在数据库中查询外部表时，默认都会在其存储的directory下产生一个日志文件，记录。

4.删除外部表：
SQL> drop table emp_ext;

Table dropped

SQL> select * from emp_ext;

select * from emp_ext

ORA-00942: 表或视图不存在

在e:\oracle下的emp文件仍然存在。

//但我们在数据库打开状态下删除物理文件是可以的!当再次浏览此表时会报错：

SQL> select * from empext t;

select * from empext t

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-11010: unable to open at least one dump file for load
ORA-06512: 在 "SYS.ORACLE_DATAPUMP", line 19

5.更改外部表：
SQL> update empext set comm='1000' where empno=7369;

update empext set comm='1000' where empno=7369

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

//证明外部表是不支持更新操作的，数据库中只能读取、导入和导出。

二、使用已产生的pump文件创建外部表

由于环境所限，我们实现在同一台机器上的不同数据库实例间进行外部表的导出和导入

1、把产生的dmp文件复制到其它目录下

C:\Documents and Settings\Administrator>expdp scott/tiger@admin directory='exp_d
ir' dumpfile=EMPEXT.DMP tables=empext

Export: Release 10.2.0.1.0 - Production on 星期三, 26 9月, 2012 14:22:58

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@admin directory='exp_dir' du
mpfile=EMPEXT.DMP tables=empext
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 0 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
已成功加载/卸载了主表 "SCOTT"."SYS_EXPORT_TABLE_01"
******************************************************************************
SCOTT.SYS_EXPORT_TABLE_01 的转储文件集为:
  E:\EMPEXT.DMP
作业 "SCOTT"."SYS_EXPORT_TABLE_01" 已于 14:23:56 成功完成

可以看到，这个操作是相对耗时的，十几条数据，用了一分钟才完成。
导出时，在e:\下产生了日志文件和复制出来的dmp文件。

2、在数据库实例admin1中创建directory：

SQL> create directory temp_dir as 'e:\';

Directory created

3、创建外部表：
SQL>  CREATE TABLE empext2
    (
  EMPNO NUMBER(4),
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2) 
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY temp_dir
     LOCATION ('EMPEXT.DMP')
   );

Table created

SQL> select * from empext2;

select * from empext2

ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-31619: 转储文件 "e:\EMPEXT.DMP" 无效
ORA-06512: 在 "SYS.ORACLE_DATAPUMP", line 19

//我们看到，虽然表创建成功，并已经找到了外部表的位置，但读取数据出错了。

SQL> drop table empext2;

Table dropped

//将转储文件删除，直接将e:\oracle下的文件EMPEXT.DMP复制到e:\，再次创建表：

SQL>  CREATE TABLE empext2
    (
  EMPNO NUMBER(4),
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2) 
    )
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY admin1
     LOCATION ('EMPEXT.DMP')
   );

Table created

SQL> select * from empext2;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 7782 CLARK      MANAGER    7839 1981-6-9      2964.50               10
 7839 KING       PRESIDENT       1981-11-17    6050.00               10
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
 7934 MILLER     CLERK      7782 1982-1-23     1573.00               10

12 rows selected

//也可以将路径直接指向e:\oracle，也可以正常查到数据。

三、如果基表发生改变，如何更新数据源：
1、先删除原数据库的外部表以及创建外部表产生的dmp文件。
2、重新创建外部表。
3、把新产生的dmp文件复制到远程主机上即可。

四、创建多个外部文件（dmp）组成的表（Create an external table with three dump files and with a degree of parallelism of three.）

创建三个dump文件的外部表，并行度3的意思是把emp的数据导出到3个文件，以后如果1个文件丢失，则查询的时候会丢失这个文件里面记录的数据：
SQL> CREATE TABLE emp_xt3
    ORGANIZATION EXTERNAL
    (                   
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY admin
      LOCATION ('emp_xt1.dmp', 'emp_xt2.dmp', 'emp_xt3.dmp')
    )                   
    PARALLEL 3          
    AS SELECT * FROM emp;

这时，将emp_xt1.dmp、emp_xt3.dmp移除后无影响，emp_xt2.dmp移除后会查询不到数据。这部分有待进一步研究，但至少不会报错。
用更多数据的表进行实验，看到：几个文件的大小不等，且内容没有冗余。

五、外部表可以通过数据文件进行合并

1、创建包含有部分数据的外部表
SQL> CREATE TABLE emp_part_xt
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY admin
    LOCATION ('emp_p1_xt.dmp')
    )
    AS SELECT * FROM emp WHERE empno < 7654;
   
SQL> drop table emp_part_xt;
 
Table dropped.
 
SQL> CREATE TABLE emp_part_xt
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY admin
    LOCATION ('emp_p2_xt.dmp')
    )
    AS SELECT * FROM emp WHERE empno >= 7654;
 
SQL> CREATE TABLE emp_part_all_xt
    (
  EMPNO NUMBER(4),
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4),
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2)
    )
    ORGANIZATION EXTERNAL
    (
   TYPE ORACLE_DATAPUMP
   DEFAULT DIRECTORY admin
   LOCATION ('emp_p1_xt.dmp','emp_p2_xt.dmp')
   );

//以上操作实际是将两个外部表文件'emp_p1_xt.dmp','emp_p2_xt.dmp'定义为表emp_part_all_xt的数据源。

六、外部表的存储
SQL> select * from dba_objects where object_name='EMPEXT';

OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
SCOTT                          EMPEXT                                                                                                               52745                TABLE               2012-9-26 1 2012-9-26 14: 2012-09-26:14:47:15 VALID   N         N         N

SQL> select * from dba_segments where segment_name='EMPEXT';

OWNER                          SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_POOL
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -----------

//通过上述查询看到，虽然外部表的定义信息是在数据库中的，但其数据并没有存储在数据库中。

七、外部表创建再述
上面的创建语句格式为：
 CREATE  TABLE table_name
    ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY directory_name
      LOCATION ('file_name')
    )
    AS SELECT statement;
另外看到的一种格式：
  create table table_name(column_name datatype ……)
  organization external (
  type oracle_loader
  default directory directory_name
  access parameters (
  records delimited by newline
  nobadfile
  nodiscardfile
  nologfile
  )
  location('file_name')
  )
  reject limit unlimited

格式3：
create table data_ext(
USER_ID VARCHAR2(20) ,
USER_NAME VARCHAR2(20),
regdate date)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY extenttable
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(user_id,user_name,
regdate date "YYYY-MM-DD HH24:MI:SS"
)
)
LOCATION('data.txt')
)

//后两种是通过sqlldr从外部文件中导入。猜想，大概sqlldr的实际语句类似这个吧。

综上，外部表可以通过数据库表或sqlldr导入文件创建；可以推出外部表的一些用途：转储数据、实现安全性和一致性；外部表的局限性在于不能进行dml操作或创建索引，但从安全角度也算是优点了。
个人认为，外部表也可以作为数据备份、迁移的一种手段（dmp格式的文件存储更加安全），避免了在使用过程中对数据的误操作，但在各个版本间的通用性上无法保证。

 

参考：http://wenku.baidu.com/view/ae70e3ea4afe04a1b071de47.html
http://www.cnblogs.com/lanzi/archive/2010/12/28/1918755.html

等等

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

转载于:http://blog.itpub.net/26451536/viewspace-745166/