oracle外部表

关于外部表的描述

---

正确描述

the create table as select statement can be used to upload data into a normal table in the database from an external table

外部表表现得就像一般表，可以命名用create table as select来创建表，同时起到upload数据的作用

1. 创建外部表必须使用 Directory 指定外部表的目的地，目录是数据库对象，相当于把物理目录映射成一个逻辑目录名
2. 引入 directory 的好处是简化了在不同 OS 中对于物理目录路径的格式描述
3. 通过 Sqlloader 和 Data pump 两种方法创建外部表时都必须使用指定的 directory

外部表数据字典

• xx_external_tables
• xx_external_locations
• xx_tables
• xx_tab_columns
• xx_directories

-- ORACLE_LOADER引擎建立外部表

create directory dir1 as '/home/oracle/dir1';
-- 生成csv格式数据，以逗号分隔，不要表并没有
-- 数据文件为emp1.dat
grant read,write on directory dir1 to public;
CREATE TABLE emp1_ext
(empno number,
ename varchar2(10),
sal number,
comm number,
deptno number)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY dir1
ACCESS PARAMETERS (fields terminated by ','
missing field values are null)
LOCATION ('emp1.dat')
);

-- ORACLE_DATADUMP引警导入导出外部表
-- 输出内容为dmp文件
CREATE TABLE emp2_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION ('emp.dmp')
)
AS SELECT empno,ename,sal,comm,deptno FROM emp;
--- 输入为dmp
CREATE TABLE emp3_ext
(
empno number,
ename varchar2(10),
sal number,
comm number,
deptno number
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir1
LOCATION ('emp.dmp')
);

---

错误描述

the default reject limit for external tables is unlimited.

REJECT LIMIT UNLIMITED
在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。默认值为零。设定为UNLIMITED则错误不受限制

• 外部数据表都是只读的，因此在外部表不能够执行DML操作，也不能创建索引。
• ANALYZE语句不支持采集外部表的统计数据，应该使用DMBS_STATS包来采集外部表的统计数据。
• 需要先建立目录对象
• 文件要有固定的格式、不能有标题列、访问时会自动创建一个日志文件

The data and metadata for an external table are stored outside the database

在创建外部表的时候，并没有在数据库中创建表，也不会为外部表分配任何的存储空间；
创建外部表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据；
由于没有存储实际数据，故无法为外部表创建索引，同时在数据使用DML时也不支持对外部表的插入、更新、删除等操作；

oracle loader and oracle datapump have exactly the same functionality when used with an external table.

一个是文本文件，主要用于跨平台数据移动，一个是二进制文件，只适用于oracle库间数据移动

Refer to: oracle 外部表