Oracle数据导入 oracle load data infile



 

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.....;  建立数据库间的 dblink，然后执行插入数据进行数据迁移，如下其中一条语句： create table B as select * from A@dblink where ... insert into B select * from A@dblink where ...   exp A 表，再 imp 到 B 表，exp 时可加查询条件 通过在程序上实现 select from A ..，然后 insert into B ...。但该方法也要分批提交，效果和方法1类似 再就是本篇推荐的方法，用 Sql Loader(sqlldr) 来导入数据，效果杠杠滴！！！ 在开始用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共同应用。