ORACLE创建外部表及数据加载
ORACLE创建外部表及数据加载
一.外部表特性
数据文件位于操作系统之外,并且具有一定的格式分割的文本文件或其他类型文件.Oracle的外部表通过SQL的形式访问数据文件中的数据,数据并不需要加载到数据库中且数据是可读的,所以不用dml操作,创建索引
二.创建外部表的步骤
a.创建目录对象,这一点限制数据必须的Server端
b.数据文件的准备
数据文件要求为操作系统之外且固定格式,不能有标题
c.创建外部表的字段如有特殊字段需用双引号 ”SYS_ID#”
d.删除外部表及目录,应先删除表后再删除目录
三.创建外部表实列
创建目录:
create or replace directory SQLDR
as 'D:\oracle\oradata\data';
创建外部表:(通过sqlldr方式的日志产生)
-- Create table
create table SYS_SQLLDR_X_EXT_MUREX_GL_TEMP
(
areano VARCHAR2(20),
currency VARCHAR2(20),
apcode VARCHAR2(20),
or易做图e VARCHAR2(20),
damount VARCHAR2(200),
camount VARCHAR2(200),
remakr VARCHAR2(200)
)
organization external
(
type ORACLE_LOADER
default directory SQLDR
access parameters
(
RECORDS DELIMITED BY 0X'0A'
BADFILE 'SQLDR':'MUREX_GLRCN_INIT_badfile.dat'
DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_disfile.dat'
LOGFILE 'murex_gl_temp.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY ' ' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"AREANO" CHAR(255)
,
"CURRENCY" CHAR(255)
,
"APCODE" CHAR(255)
,
"OR易做图E" CHAR(255)
,
"DAMOUNT" CHAR(255)
,
"CAMOUNT" CHAR(255)
,
"REMAKR" CHAR(255)
)
)
location (SQLDR:'MUREX_GLRCN_INIT_20190831.dat')
)
reject limit UNLIMITED;
四.sqlldr产生外部表的ddl语句
在命令运行sqlldr(控制文件事先要准备好):
a.用sqlldr生成log文件
sqlldr user_name/password@oradb control=control_file.ctl external_table=gernerate_only;
NOT_USED:默认值。
EXECUTE:这个值说明SQLLDR不会生成并执行一个SQLINSERT语句;而是会创建一个外部表,且使用一个批量SQL语句来加载。
GENERATE_ONLY:使SQLLDR并不具体加载任何数据,而只是会生成所执行的SQL DDL和DML语句,并放到它创建的日志文件中。
注:DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE,则会加载数据,而不会生成外部表。
D:\oracle\oradata\data>sqlldr boc_rwa3/a@rwadb control=murex_gl_temp.ctl externa
l_table=generate_only
SQL*Loader: Release 11.2.0.3.0 - Production on 星期四 9月 12 22:27:03 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
b.从log文件中提取ddl语句
CREATE TABLE "SYS_SQLLDR_X_EXT_MUREX_GL_TEMP"
(
"AREANO" VARCHAR2(20),
"CURRENCY" VARCHAR2(20),
"APCODE" VARCHAR2(20),
"OR易做图E" VARCHAR2(20),
"DAMOUNT" VARCHAR2(200),
"CAMOUNT" VARCHAR2(200),
"REMAKR" VARCHAR2(200)
)
ORGANIZATION external
(
TYPE oracle_loader --指定外部表的访问方式,9i不支持oracle_datapump
DEFAULT DIRECTORY SQLDR --目录
ACCESS PARAMETERS --配置外部表参数
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK --记录以换行为结束
BADFILE 'SQLDR':'MUREX_GLRCN_INIT_20130809_badfile.dat' --存放处理失败的记录文件描述
DISCARDFILE 'SQLDR':'MUREX_GLRCN_INIT_20130809_disfile.dat' --存放处理丢弃的记录文件描述
LOGFILE 'murex_gl_temp.log_xt' --日志文件
READSIZE 1048576 --Oracle读取输入数据文件所用的默认缓冲区,此处为MB,如专用模式则从PGA分配,如共享模式 则从SGA分配
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY " " LDRTRIM --"|"描述字段的终止符, " "作用符
REJECT ROWS WITH ALL NULL FIELDS -- --所有为空值的行被跳过并且记录到bad file
( ---下面是描述外部文件各个列的定义
"AREANO" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"CURRENCY" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"APCODE" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"OR易做图E" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"DAMOUNT" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"CAMOUNT" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " ",
"REMAKR" CHAR(255)
TERMINATED BY "|" OPTIONALLY ENCLOSED BY " "
)
)
location
(
'MUREX_GLRCN_INIT_20130809.dat' --描述外部文件的文件名
)
)REJECT LIMIT UNLIMITED --描述允许的错误数,此处为无限制
;
这里特别提示一下换行符:因为在处理这个问题时,处理一天才把问题找到:
windows换行是\r\n,十六进制数值是:0D0A
LINUX换行是\n,