外部表（一）

外部表概述

外部表是对sql*loader的一种补充，是在数据库中只保存表的定义，而数据可以文件的形式保存在数据库之外的操作系统上，数据源文件和表之间维持映射关系。

怎样创建外部表

使用语句CREATE TABLE...ORGANIZATION EXTERNAL创建外部表

必须声明的属性：

type：有两个可用类型oracle_loader和oracle_datapump

  ORACLE_LOADER:默认为这个访问驱动，仅仅实现数据的加载，并且数据必须在文本文件中。Loads from external tables to internal tables are done by reading from the external tables' text-only datafiles.

  ORACLE_DATAPUMP：能实现数据的加载和卸载，数据必须来源于二进制的dump文件

DEFAULT DIRECTORY：指定外部表的读写路径

ACCESS PARAMETERS：设定数据源文件和表中行之间的映射关系；

LOCATION：指定数据文件的存放目录和文件名

example1：

Creating and Loading an External Table Using ORACLE_LOADER

1.外部数据文件：

info.dat

[oracle@secdb1 ~]$ vi info.dat

56november, 15, 1980  baker             mary       alice     09/01/2004
87december, 20, 1970  roper             lisa       marie     01/01/1999
~

 

2.创建目录并授权

SYS@PROD> create directory ext_tab_dir as '/home/oracle';

Directory created.

SYS@PROD> grant read on directory ext_tab_dir to scott;

Grant succeeded.

 

3创建一个名为emp1的普通表

SYS@PROD> conn scott/tiger
Connected.
SCOTT@PROD> create table emp1 (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_initial CHAR(1), hire_date DATE, dob DATE);

Table created.

 

4，创建一个名为emp_load的外部表

SCOTT@PROD> CREATE TABLE emp_load
  2        (employee_number      CHAR(5),
  3         employee_dob         CHAR(20),
  4         employee_last_name   CHAR(20),
  5         employee_first_name  CHAR(15),
  6         employee_middle_name CHAR(15),
  7         employee_hire_date   DATE)
  8      ORGANIZATION EXTERNAL
  9        (TYPE ORACLE_LOADER
 10        DEFAULT DIRECTORY def_dir1
 11        ACCESS PARAMETERS
 12          (RECORDS DELIMITED BY NEWLINE
 13           FIELDS (employee_number      CHAR(2),
 14                   employee_dob         CHAR(20),
 15                   employee_last_name   CHAR(18),
 16                   employee_first_name  CHAR(11),
 17                   employee_middle_name CHAR(11),
 18                   employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19                  )
 20          )
 21        LOCATION ('info.dat')
 22       );

Table created.

 

5，查询外部表

SCOTT@PROD> select * from emp_load;

EMPLO EMPLOYEE_DOB         EMPLOYEE_LAST_NAME   EMPLOYEE_FIRST_ EMPLOYEE_MIDDLE EMPLOYEE_
----- -------------------- -------------------- --------------- --------------- ---------
56    november, 15, 1980   baker                mary            alice     0     01-SEP-04
87    december, 20, 1970   roper                lisa            marie     0     01-JAN-99

 

6，加载外部表中的数据到emp1中

SCOTT@PROD> INSERT INTO emp1 (emp_no,
  2                       first_name,
  3                       middle_initial,
  4                       last_name,
  5                       hire_date,
  6                       dob)
  7      (SELECT employee_number,
  8              employee_first_name,
  9              substr(employee_middle_name, 1, 1),
 10              employee_last_name,
 11              employee_hire_date,
 12              to_date(employee_dob,'month, dd, yyyy')
 13      FROM emp_load);

2 rows created.

 

7，验证

SCOTT@PROD> select * from emp1;

EMP_NO LAST_NAME                 FIRST_NAME           M HIRE_DATE DOB
------ ------------------------- -------------------- - --------- ---------
56     baker                     mary                 a 01-SEP-04 15-NOV-80
87     roper                     lisa                 m 01-JAN-99 20-DEC-70

 

说明：已经完全把emp_load中操作系统上文件的数据加载到表emp1中，实现了类似sql*loader的功能

 

使用oracle_pump加载数据并生成外部文件

SCOTT@PROD> create table emp_ext
  2       (empno,ename,deptno)
  3       organization external
  4       (
  5       type oracle_datapump
  6       default directory def_dir1
  7       location('emp1.dat')
  8       )
  9       as
 10       select empno,ename,deptno from emp;

Table created.

SCOTT@PROD> select * from emp_ext;

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7788 SCOTT              20
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10

14 rows selected.

 

在操作系统上查看

[oracle@secdb1 ~]$ ll | grep emp1.dat
-rw-r-----  1 oracle oinstall 12288 Apr  1 17:27 emp1.dat