Oracle数据的导入导出相信很多时候,在不同数据库的表间记录进行复制或迁移时我们都会涉及到!!!废话不多说,这里我列举一下几种常见的操作方法:
-
A库的table 表的记录导出为一条条分号隔开的 insert 语句文件,然后执行文件插入到 B 库的table表中
该方法的缺点就是,需要分批COMMIT(ORACLE是需要手动COMMIT的哈,别说你不晓得!),每次COMMIT大概三五百条这样子,不然我们的客户端sql developer估计要卡死,半天没反应。该方式的INSERT,ORACLE数据库都会产生相应的REDO LOG(我们知道,ORACLE是以事务出了名的,REDO LOG是为确保已经提交的事务不会丢失而建立的一个机制),这也是导致如果逐条导入而效果低下的主要原因。(10万条数据,每条commit一次,就10万条REDO LOG呀!!!) 这里,随便写一下如果对数据准确性要求不是很高的话,不产生REDO LOG的操作语句: ----关闭表B REDO LOG ALTER TABLE B nologging; ----方法1:通过INSER INTO VALUES 方式插入数据 INSERT INTO B(c1,c2) VALUES(x,xx); ----通过DBLINK的方式直接把A表数据插入到B表 INSERT INTO B SELECT * FROM A@dblink WHERE.....;
在开始用Sql Loader前,我们要先有这样一个思路:sqllldr是Oracle提供的一个服务端操作命令,它是通过执行一个控制脚本(后缀名为 .ctl 的文件),去加载相应的数据(这些就是我们要迁移的数据,它们可以一起放在 .ctl文件中,也可以单独一个数据文件,这两种不同的数据位置,就是接下来要说的两种使用方法)到指定的表中。 下面我先贴上一个控制脚本给读者先熟悉一下,心里有个底先。具体说明不要着急,我会在下面的两种使用方法中详细说明。
OPTIONS(direct=true,ERRORS=50,skip=0,ROWS=500000, readsize=20971520, bindsize=20971520) Load data infile 'test.txt' "str'****@@@@'" truncate into table action_c fields terminated by '@#$' optionally enclosed by '%%%%%' ( ACTION_ID, Time DATE 'YYYY-MM-DD HH24:MI:SS', TBSN, CSN, SYSTEM, BLOCK, UPID, AGENT, OFFICE, UGR, TXNID, PID, STEP DATE 'YYYY-MM-DD HH24:MI:SS', IDNBR, HOST, FUNC, ULVL, CUST, Input, LINENUM, ISCLOB, THECLOB CHAR(50000000) )
先熟悉一下sqlldr命令参数的含义,进入Windows CMD,命令行下执行 Oracle 的 sqlldr 命令,可以看到它的详细参数说明,着重关注以下几个参数:
- userid -- Oracle 的 username/password[@servicename]
- control -- 控制文件,可能包含表的数据
- log -- 记录导入时的日志文件,默认为 控制文件(去除扩展名).log
- bad -- 坏数据文件,默认为 控制文件(去除扩展名).bad
- data -- 数据文件,一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
- errors -- 允许的错误记录数,可以用它来控制不允许一条记录出错
- rows -- 多少条记录提交一次,默认为 64
- skip -- 跳过的行数,比如的数据文件的前面几行(表头或其他描述信息)
重点来了~ 重点来了~ 重点来了 sqlldr 的使用有两种方法,以下分别举例来演示
- 使用一个包含数据的控制文件
- 使用一个控制文件(作为脚本) 和 一个单独的数据文件
说明:一般为了利于脚本和数据的分离,会使用第二种方式。 准备材料:数据文件(可以是 CSV 文件或者 带数据分割符分隔,如"|" ,"/" 的txt文件) 数据文件可以用 PL/SQL Developer 或者 Toad 导出,也可以用 SQL *Plus 的 spool 格式化产出,或是 UTL_FILE 包生成。另外,用 Toad 还能直接生成包含数据的控制文件。 首先,假设我们有这么一个张表 users,并插入五条记录, 做为材料数据:
create table users( user_id number, --用户 ID user_name varchar2(50), --用户名 login_times number, --登陆次数 last_login date --最后登录日期 );
insert into users values(1,'Unmi',3,sysdate); insert into users values(2,NULL,5,to_date('2008-10-15','YYYY-MM-DD')); insert into users values(3,'隔叶黄莺',8,to_date('2009-01-02','YYYY-MM-DD')); insert into users values(4,'Kypfos',NULL,NULL); insert into users values(5,'不知秋',1,to_date('2008-12-23','YYYY-MM-DD'));
commit;
第一种方式:使用一个包含数据的控制文件 【1】建立一个控制文件 users.ctl, 完整的 users.ctl 文件内容是:
OPTIONS (skip=1,rows=128)
LOAD DATA
INFILE *
append
INTO TABLE users
when LOGIN_TIMES<>'8'
Fields terminated by ","
trailing nullcols
(
virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号
user_id "user_seq.nextval", --这一列直接取序列的下一值,而不用数据中提供的值
user_name "'Hi '||upper(:user_name)", --还能用SQL函数或运算对数据进行加工处理
login_times terminated by ",", NULLIF(login_times='NULL') --可为列单独指定分隔符
last_login DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL
)
BEGINDATA --数据从这里开始
,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN
1,1,Unmi,3,2009-1-5 20:34
2,2,Fantasia,5,2008-10-15
3,3,隔叶黄莺,8,2009-1-2
4,4,Kypfos,NULL,NULL
5,5,不知秋,1,2008-12-23
以下是脚本的详细说明:
-- OPTIONS:就是之前在CMD窗口看到的参数,命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
--INFILE:因为数据同控制文件在一起,所以这里要用 *
--append : 操作类型,这里用了 append 来操作,在表 users 中附加记录
-- when LOGIN_TIMES<>'8' :用 when 子句选择导入符合条件的记录
--INTO TABLE users:要插入记录的表
--Fields terminated by ",":数据中每行记录用 "," 分隔
--trailing nullcols:表的字段没有对应的值时允许为空
--virtual_column FILLER:这是一个虚拟字段,用来跳过由 PL/SQL Developer 生成的第一列序号
--last_login DATE "YYYY-MM-DD HH24:MI:SS" :指定接受日期的格式,相当用 to_date() 函数转换
append 说明:在操作类型 append 位置, 除了可以用append ,还可用以下中的任意一值: 1) insert --为缺省方式,在数据装载开始时要求表为空 2) append --在表中追加新记录 3) replace --删除旧记录(用 delete from table 语句),替换成新装载的记录 4) truncate --删除旧记录(用 truncate table 语句),替换成新装载的记录
【2】执行控制脚本:
sqlldr dbuser/dbpass@dbservice control=users.ctl
【3】控制台会显示信息:
C:\>sqlldr dbuser/dbpass@dbservice control=users.ctl
SQL*Loader: Release 9.2.0.1.0 - Production on 星期三 1月 7 22:26:25 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
达到提交点,逻辑记录计数4 达到提交点,逻辑记录计数5
第二种方式: 使用一个控制文件(作为脚本) 和一个数据文件 【1】首先,建立数据文件,我们这里用 PL/SQL Developer 导出表 users 的记录为 users_data.csv 文件,内容如下:
" ","USER_ID","USER_NAME","LOGIN_TIMES","LAST_LOGIN"
"1","1","Unmi","3","2009-1-5 20:34:44"
"2","2","","5","2008-10-15"
"3","3","隔叶黄莺","8","2009-1-2"
"4","4","Kypfos","",""
"5","5","不知秋","1","2008-12-23"
该数据文件是以逗号,做为分隔符,并用双引号" 括字段名和数据(这些到时会在控制脚本中做相应的代码说明) 【2】建立一个控制文件 users.ctl,脚本内容如下:
OPTIONS (skip=1,rows=128)
LOAD DATA
INFILE "users_data.csv"
TRUNCATE
INTO TABLE users
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
(
virtual_column FILLER,
user_id number,
user_name,
login_times,
last_login DATE "YYYY-MM-DD HH24:MI:SS"
)
以下是脚本的详细说明:
-- OPTIONS:就是之前在CMD窗口看到的参数,命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
--INFILE:指定外部数据文件,如果有多个数据文件,可以写多个 INFILE "another_data_file1.csv" ,这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件
--TRUNCATE: 操作类型,执行插入记录前的清表动作
--INTO TABLE users:要插入记录的表
--Fields terminated by ",":数据中每行记录用 "," 分隔
--Optionally enclosed by '"' : 数据中的字段用 双引号 " 括起
--trailing nullcols:表的字段没有对应的值时允许为空
--virtual_column FILLER:这是一个虚拟字段,用来跳过由 PL/SQL Developer 生成的第一列序号
--user_id number:字段可以指定类型,否则认为是 CHARACTER 类型, log 文件中有显示
--last_login DATE "YYYY-MM-DD HH24:MI:SS" :指定接受日期的格式,相当用 to_date() 函数转换
TRUNCATE说明:在操作类型 TRUNCATE位置, 除了可以用TRUNCATE,还可用以下中的任意一值: 1) insert --为缺省方式,在数据装载开始时要求表为空 2) append --在表中追加新记录 3) replace --删除旧记录(用 delete from table 语句),替换成新装载的记录 4) truncate --删除旧记录(用 truncate table 语句),替换成新装载的记录
【3】执行操作命令:
sqlldr dbuser/dbpass@dbservice control=users.ctl
最后我们查看一下,确认一下数据是否有被导入。在 dbservice 的数据库的表 users 中记录就和数据文件中的一样了。
执行完 sqlldr 后希望能留意一下生成的几个文件,如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件,从中可让你更好的理解 Sql Loader,里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。
上面的控制文件包含的内容比较复杂(演示目的),请根据注释理解每个参数的意义。还能由此发掘更多用法。
最后说下有关 SQL Loader 的性能与并发操作 【1】OPTIONS 中的 ROWS参数默认值为 64,你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。 (体验过在 PL/SQL Developer 中一次执行几条条以上的 insert 语句的情形吗?) 【2】 常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中,可以提高导入数据的 性能。当然,在很多情况下,不能使用此参数(如果主键重复的话会使索引的状态变 UNUSABLE!)。 【3】通过指定 UNRECOVERABLE选项,可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 DIRECT一起使用。 【4】 对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务,如下: sqlldr userid=/ control=result1.ctl direct=true parallel=true sqlldr userid=/ control=result2.ctl direct=true parallel=true sqlldr userid=/ control=result2.ctl direct=true parallel=true 当加载大量数据时(大约超过10GB),最好抑制日志的产生:
SQL> ALTER TABLE RESULTXT nologging;
这样不产生REDO LOG,可以提高效率。然后在 CONTROL 文件中 load data 上面加一行:unrecoverable, 此选项必须要与DIRECT共同应用。 |