一、概述
概念：外部表作为SQL*Loader补充功能，可以使你访问外部数据源的数据像一个数据库表一样访问。
类型：外部表分为两种类型，分别为ORACLE_LOADER、ORACLE_DATAPUMP，各自有自己的访问驱动
ORACLE_LOADER访问驱动程序是默认的。它加载数据从外部表的内部表。数据必须来自文本数据文件。（ORACLE_LOADER访问驱动程序可以不执行卸载;也就是说，它不能从一个内部表中移动数据到外部的表。）
的ORACLE_DATAPUMP访问驱动程序可以同时执行加载和卸载。必须将该数据从二进制转储文件。从外部表加载到内部表的二进制转储文件中读取。从内部表外部表卸载都是由外部表中填充的二进制转储文件。的ORACLE_DATAPUMP访问驱动器可以写入转储文件创建外部表与SQL CREATE TABLE AS SELECT语句的一部分。一旦转储文件被创建时，它可以读出任意次数的，但它不能被修改（即，可以进行没有DML操作）。
二、操作实例
- 建立sqlldr类型的外部表
- 建立目录对象
mkdir -p /u01/app/oracle/data
create directory d1 as '/u01/app/oracle/data'
grant all on directory d1 to public ;
查找目录：select * from dba_directories;
- 数据源
vim a.txt
1,a,wh
2,b,sh
3,c,bj
- 控制文件
LOAD DATA
infile a.txt
append
INTO TABLE SCOTT.P
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
PID INTEGER EXTERNAL,
PNAME CHAR,
LOCATION CHAR
)
- 借助sqlldr语法生成创建语法
sqlldr scott/oracle control=a.ctl log=a.log external_table=GENERATE_ONLY
在a.log中找到建立外部表的语法
CREATE TABLE scott.waibu
(
"PID" NUMBER,
"PNAME" VARCHAR2(20),
"LOCATION" VARCHAR2(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY d1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
LOGFILE 'a.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"PID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"PNAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"LOCATION" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'a.txt'
)
)REJECT LIMIT UNLIMITED
- 建立datapump外部表
- 建立目录对象
mkdir -p /u01/app/oracle/data
create directory d1 as '/u01/app/oracle/data'
grant all on directory d1 to public ;
查找目录：select * from dba_directories;
- 卸载数据
CREATE TABLE emp_CS
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY d1
LOCATION ('emp.dmp')
)
AS SELECT * FROM emp;
emp_2 是个外部表，emp.dmp操作的数据文件
- 获取定义语法
set long 10000--定义输出格式
select DBMS_METADATA.GET_DDL('TABLE','EMP_CS','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP_CS"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "D1"
LOCATION
( 'emp.dmp'
)
)
- 把语法 和emp.dmp 移动目标服务器
