mysql外部表加载_外部表加载数据(ExternalTables)

支持数据库版本：10gR2

1 选用 sqlldr 而不是外部表的情况

1-1 必须通过网络加载数据, 输入文件不在数据库服务器上

1-2 多用户使用相同的外部表处理不同的输入文件

1-3 LOB 类型

2 创建外部表 (利用 sqlldr 控制文件创建)

sqlldr go/go ldr.ctl external_table=generate_only

如果使用 direct=true 则会覆盖 external_table=generate_only 选项

3 创建Directory对象

create directory extdir as '/tmp/script';

grant read,write on directory extdir to go;

4 创建外部表

4-1 oracle_loader 方式

每次访问外部表产生的log, bad, dsc文件也可以通过外部表形式读取

create table ext_tbl (col1 varchar2(10),col2 number)

organization external

(

type oracle_loader default directory extdir

access parameters

(

records delimited by newline

fields terminated by ","

(col1,col2)

)

location('test.dat')

);

4-2 oracle_datapump 方式

可以加载, 卸载(导出)数据

只会产生log文件, 没有bad, dsc文件

- 修改外部表

-- 表结构

于普通表相同，修改后access parameters中也要作相应修改

-- 访问驱动

alter table ext_tbl default directory dir_name;

-- 加载参数

要修改的部分所有参数必须重新定义

alter table ext_tbl access parameters (records delimited by newline fields terminated by ":" (col1,col2));

-- 加载路径

alter table ext_tbl location ('filename');

- 加载多个文件

各个文件格式必须能被access parameters定义所识别

alter table ext_tbl location ('filename1','filename2');

- 加载效率优化

parallel,nologfile,nodiscardfile,nobadfile

sqlldr中的优化参数也同样有效(访问驱动为oracle_loader时)

insert /*+ append */ into tbl select ＊ from ext_tbl; ---归档并且forcelogging时append失效

- 加载驱动

-- oracle_loader

--- 格式化记录

记录分隔方式

records fixed 20  --- 定长，20位产生一条记录

records variable 4 --- 行记录前4位代表此行长度

records delimited by newline --- 换行符为一条记录终结

records delimited by '|' --- | 为一条记录终结

加载条件

load when (col1 != 1)   --- col1 != 1 的记录加载

输出文件

logfile 'filename'/nologfile

badfile 'filename'/nobadfile

discardfile 'filename'/nodiscardfile

缓冲区

readsize   --- 默认1M

date_cache --- 默认1000条

跳过记录数

skip 10

--- 处理数据行

fields terminated by ','  --- 列分隔

missing field values are null --- 某列没有值则以NULL加载

reject rows with all null fields --- 所有列都没值则跳过该记录，不指定则各列均以NULL加载

--- 列定义

col1 (1:5) char(5) --- 列名，长度(仅定长)，类型，默认值

-- oracle_datapump ???