Oracle 外部表

一、外部表的结构
外部表是以文件形式存储在 Oracle DB 外的操作系统上的只读表。
外部表的 DDL 由两部分组成：一部分描述 Oracle DB 的列类型，另一部分描述如何将外部数据映射到 Oracle DB 的数据列。



二、外部表的优点
• 可直接使用外部文件中的数据或将数据加载到另一个数据库。
• 可以同时查询外部数据和数据库中驻留的表，并可将外部数据与数据库中的表直接联接，而不必先加载外部数据。
• 复杂查询的结果可卸载到外部文件中。
• 可组合来自不同源的已生成文件在加载中使用。


三、外部表相关的数据字典
• DBA_EXTERNAL_TABLES：数据库中外部表的特定属性。
• DBA_EXTERNAL_LOCATIONS：外部表的数据源。
• DBA_DIRECTORIES：描述数据库中的目录对象。


四、外部表的使用
1、首先创建directory
create or replace directory my_dir as '/data';

Directory created.

SQL> select * from dba_directories where DIRECTORY_NAME='MY_DIR';

OWNER DIRECTORY_NAME DIRECTORY_
------------------------------ ------------------------------ ----------
SYS MY_DIR /data

2、授权用户使用该directory
SQL> grant read,write on directory my_dir to scott;

Grant succeeded.

3、编造2个文件a.txt、b.txt并放到对应的directory中
[root@ora11g ~]# su - oracle
[oracle@ora11g ~]$ cd /data
[oracle@ora11g data]$ vi a.txt
[oracle@ora11g data]$ cat a.txt

root:x:0:root
bin:x:1:root,bin,daemon
daemon:x:2:root,bin,daemon

[oracle@ora11g data]$ cat b.txt
exim:x:93:
oinstall:x:1000:
dba:x:1001:oracle

4、创建外部表
SQL> conn scott/tiger
Connected.

create table ext_group (name varchar2(20),passwd varchar2(20),gid number,member varchar2(40))
organization external (
type oracle_loader
default directory my_dir
access parameters(
records delimited by newline
fields terminated by ':'
missing field values are null (name char(20),passwd char(20),gid char(20),member char(40))
)
location ('a.txt','b.txt')
) reject limit 4000;

Table created.

SQL>

5、查询外部表
SQL> select * from ext_group;

NAME PASSWD GID MEMBER
-------------------- -------------------- ---------- ----------------------------------------
root x 0 root
bin x 1 root,bin,daemon
daemon x 2 root,bin,daemon
exim x 93
oinstall x 1000
dba x 1001 oracle

6 rows selected.

6、把外部表的数据转入普通表
SQL> create table aa as select * from ext_group;

Table created.

SQL> select * from aa;

NAME PASSWD GID MEMBER
-------------------- -------------------- ---------- ----------------------------------------
root x 0 root
bin x 1 root,bin,daemon
daemon x 2 root,bin,daemon
exim x 93
oinstall x 1000
dba x 1001 oracle

6 rows selected.



例子：
CREATE TABLE extab_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER DEFAULT DIRECTORY dump_dir
ACCESS PARAMETERS
( records delimited by newline
badfile dump_dir:'empxt%a_%p.bad'
logfile dump_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( employee_id, first_name, last_name,
hire_date char date_format date mask "dd-mon-yyyy"))
LOCATION ('empxt1.dat', 'empxt2.dat') )
PARALLEL REJECT LIMIT UNLIMITED;

--通过使用 ORACLE_DATAPUMP 填充外部表
CREATE TABLE ext_emp_query_results
(first_name, last_name, department_name)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dump_dir
LOCATION ('emp1.exp','emp2.exp')
)
PARALLEL
AS
SELECT e.first_name,e.last_name,d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND
d.department_name in

('Marketing', 'Purchasing');