外部表加载数据(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 ???

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22558114/viewspace-1097763/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22558114/viewspace-1097763/