数据导入导出Oracle数据库
临近春节，接到了一个导入数据的任务，在Linux客户端中的数据有50G，大约3亿3千万行；
刚开始很天真，把原始的txt/csv文件用sh脚本转化成了oralce 的insert into 语句，然后分割原始文件，用sqlplus@文件名导入。
step1 txt 转化为sql cnt=0 cat /nfsdata/table/dayinsert.txt | awk -F' ' '{ if (cnt<100) { cnt=cnt+1; } else { cnt=0; print "commit;"} print "insert into test_bd (day, daynight, daytomo) values ('\''" $1"'\'','\''"$2"'\'','\''"$3"'\'');"}' >> dayinsert.sql & echo "commit;" step 2 sql 上传到 db nohup /u01/oracle/app/oracle/product/11.2.0/client/bin/sqlplus "user_name/password"@service_name @ dayinsert.sql >>dayinsert.log 2>&1 &
结果，第二天直接把oracle数据搞趴掉了。
时间越来越短，心情越来越糟。。。。。。经过一番折腾，同事给了sqlldr连接工具，尝试下；
load data infile * append into table test_table ##要插入的数据库表名 FIELDS TERMINATED BY ',' ##注意分割符号 TRAILING NULLCOLS ( 字段1， 字段2， 等等 ) 把上述代码放入文件 filename.ctl 文件中 导入命令 sqlldr userid="user_name/password"@service_name control=finename.ctl data=/test.txt
该方法很快完成了我的数据导入，而且bug很少。推荐大家使用。
如果要导入的文件中，要导入的字段和表的字段顺序不一样，可采取控制；FILLER_1 FILLER 可把文件中不需要导入的字段忽略掉；
load data infile * append into table test_table FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( msid, timestamp, lac, cell, FILLER_1 FILLER, 忽略的字段1 FILLER_2 FILLER, 忽略的字段2 FILLER_3 FILLER, 忽略的字段3 FILLER_4 FILLER, 忽略的字段4 flag )
如果想要把多个小文件同时导入一张表中，怎么办呢？
方法1 把小文件合并成一张大文件，然后用上面的方法导入；
方法2
nohup sqlldr userid="user/password"@sevname control= seq_1.ctl data=/data1.txt parallel=true & nohup sqlldr userid="user/password#"@sevname control= seq_2.ctl data=/data2.txt parallel=true & nohup sqlldr userid="user/password#"@sevname control= seq_3.ctl data=/data3.txt parallel=true & nohup sqlldr userid="user/password#"@sevname control= seq_4.ctl data=/data4.txt parallel=true &
如果导入的data文件很小，还可以加上direct=true选项，可以增加导入的速度；
SQL*Loader是oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于从多种平面文件格式向Oracle数据库中加载数据。SQLLDR可以在极短的时间内加载数量庞大的数据。它有两种操作模式： 传统路径（conventional path）:SQLLDR 会利用SQL插入为我们加载数据。 直接路径（direct path）:采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块，而绕过整个SQL引擎和UNDO生成，同时还可能避开REDO生成。要在一个没有任何数据的库中充分加载数据，最快的方法就是采用并行直接路径加载。 常规路径装载使用SQL INSERT语句和内存中的键数组缓存（bond array buffers）将数据装载到Oracle数据库的表中。这个过程与其他进程竞争SGA内部的内存资源。如果数据库已经有支持多个并发处理进程的开销，常规路径装载会降低装载的性能。 使用常规路径装载的另外一个开销是装载进程必须搜索数据库，以查找被装载表的部分填充块，并试图填充这些块。这对日常的事务处理是非常有效的，但是它是常规路径装载的一个额外开销。 最好或有时必须使用常规路径装载方法，而不能使用直接路径装载的情形： 1、如果被装载的表是被索引的并且被并发访问的，或者如果要对表进行插入或删除，必须使用常规路径装载。 2、当在控制文件中使用SQL函数时，必须使用常规路径装载。当使用直接路径装载时，SQL函数将不适用。 3、当装载的表是一个簇表时。 4、当装载少量记录到一个大型索引表，或当表具有引用完整性或检查约束时。 5、当装载工作是通过SQL * Net或Net8在不同的平台上进行时，为使用直接路径装载，两个节点必须属于同一个计算机家族并且使用同样的字符集。 不需要使用SQL INSERT语句和键数组缓存，直接路径装载格式化输入数据到Oracle数据块并将它们直接写入数据库中。注意直接路径装载总是在表的最高水位之上插入数据，这种方式消除了用于搜索部分填充块的时间。 SQLLDR是一个命令工具，并非一个API，不能从PL/SQL调用。 SQL*Loader具有很多功能，包括以下能力： 可以从不同文件类型的多个输入数据文件中加载数据； 输入记录可以是定长的或变长的记录； 可以在同一次运行中加载多个表，还可以逻辑地将选定的记录载入到每个表中； 在输入数据载入表之前，可以对其使用SQL函数； 多个物理记录可以被编译成一个逻辑记录，同样，SQL可以提取一条物理记录并把它作为多个逻辑记录加载； 支持嵌套、嵌套表、VARRAYS和LOBS（包括BLOGCLOBNLOBBFILE）。 SQL*Loader 组件： 0.控制文件 控制文件中包含描述输入数据的信息（如输入数据的布局、数据类型等），另外还包含有关目标表的信息，控制文件甚至还可以包含要加载的数据。 1. SQL*Loader输入数据： SQL *Loader能够接收多种不同格式的数据文件。文件可以存储在磁盘或磁带上，或记录本身可以被嵌套到控制文件中。记录格式可以是定长的或变长的，定长记录是指这样的记录：每条记录具有相同的固定长度，并且每条记录中的数据域也具有相同的固定长度、数据类型和位置 2.SQL*Loader输出： （1）LOAD DATA （2）INFILE * （3）INTO TABLE DEPT （4）FIELDS TERMINATED BY ‘,’ （5）(DEPTNO,DNAME,LOC) （6）BEGINDATA （7）10,Sales,Virginia （8）20,Accounting,Virginia （9）30,Consulting,Virginia （10）40,Finance,Virginia LOAD DATA(1):这会告诉SQLLDR要做什么（在这个例子中，则指示要加载数据）。SQLLDR还可以执行CONTINUE_LOAD,也就是继续加载。只有在继续一个多表直接路径加载时才能使用后面这个选项。 INFILE * (2):这会告诉SQLLDR所要加载的数据实际上包含在控制文件中，如第6-10行所示。也可以指定包含数据的另一个文件的文件名。如果愿意可以使用一个命令行参数覆盖这个INFILE语句。［命令行选项会覆盖控制文件设置］。 INTO TABLE DEPT(3)：这告诉SQLLDR要把数据加载到哪个表中。 FILEDS TERMINATED BY ‘,’(4)：告诉SQLLDR数据的形式应该是用逗号分隔的值。 （DEPTNO,DNAME,LOC）(5)：告诉SQLLDR所要加载的列，这些列在输入数据中的顺序以及数据类型。这是指输入流中数据的数据类型，而不是数据库中的数据类型，在这个例子中，列的数据类型默认为CHAR(255)。 BEGINDATA(6)：告诉SQLLDR你已经完成对输入数据的描述，后面的行（第7-10行）是要加载到DEPT表的具体数据。 要使用以上的控制文件，建立一个空的DEPT表： CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT DEPT_PK PRIMARY KEY, DNAME VARCHAR2(14), LOC VARCHAR2(13) ); 并运行以下命令： Sqlldr userid=/ control=demo1.ctl 表装载的方法： INSERT 这是缺省方法。该方法假设在数据装载前表是空的，如果在表中有记录，SQLLDR退出，并报：SQLLDR-601: FOR INSERT OPTION,TABLE MUST BE EMPTY,ERROR ON TABLE DEPT APPEND这种方法允许记录被添加到数据库表中，而且不影响已经存在的记录 REPLACE 这种方法首先删除表中已经存在的记录，然后开始装载新的记录。注意，当老记录被删除时，表上的任意删除触发器将被触发 TRUNCATE 这种方法在装载数据前，使用SQL命令TRUNCATE 删除老的记录，因为去除了触发器的触发并且没有创建回滚，所以这种方法要比REPLACE快得多。为了使用这种方法约束必须被禁止，并且要授予特定的权限 如何加载定界数据？ 定界数据，（delimited data）即用某个特殊字符分隔的数据。 Example: FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”’ 上面例子指定用逗号分隔数据字段，每个字段可以用双引号括起。 TERMINATED BY X’9’(使用16进制格式的制表符；采用ASCII时，制表符为9) TERMINATED BY WHITESPACE 如何加载固定格式数据？ 通常会有一个由某个外部系统生成的平面文件，而且这是一个定长文件，其中包含着固定位置的数据（POSITIONAL DATA）.要加载定宽的固定位置数据，将会在控制文件中使用POSITION关键字： LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position(1:2), DNAME position (3:16), LOC position (17:29) ) BEGINDATA 10ACCOUNTIN Virginia ,USA 可以使用相对偏移量。 DEPTNO position(1:2), DNAME position(*:16) 表示DNAME 从3-16个字符。 如何加载日期？ 只需要控制文件中DATE数据类型，并指定要使用的日期掩码。这个日期掩码与数据库中的TO_CHAR和TO_DATE中使用的日期掩码是一样。 如何使用函数加载数据？ 只需要将函数加到控制文件中 （DNAME “UPPER(:dname)” ） TRAILING NULLCOLS 会导致绑定变量成为NULL,如果输入记录中不存在某一列的数据，SQLLDR会为该列绑定一个NULL值。 下面是增加SQL*Loader性能的一些补充技巧： 1）使用定位域而不要使用分隔域，分隔域要求装载器搜索数据以查找分隔符。定位域比较快，因为装载器只需要做简单的指针运算。 2）为终止域指定最大长度，使每个捆绑数组更为有效地插入。 3）预分配足够的存储空间。当数据被装载时，表中需要更多的空间， Oracle分配更多的区间以容纳数据，如果在数据装载期间频繁地做这项操作，处理的开销将非常大。在装载之前计算或估算存储空间需求能够让你预先创建必要的存储空间。 4）如果可能，在控制文件中尽量避免使用NULLIF和DEFAULTIF子句。这两个子句对于被装载的每条记录都会引起列运算。 5）分割数据文件，并行运行常规路径装载。 6）通过使用命令行参数ROWS，减少提交次数。 7）避免不必要的字符集转换，确保客户端的NLS_LANG环境与服务器端的相同。 8）只要可能，尽量使用直接路径装载方法。 9）当使用直接路径装载方法时，为表的最大索引预先排序并使用SORTED INDEXES子句。 10）当使用直接路径装载方法时，尽量使用并行直接路径选项。 11）在直接路径装载期间，尽可能少使用重做日志。有三种不同级别的控制实现这点： 禁止数据库归档； 在控制文件中使用关键字UNRECOVERABLE； 使用NOLOG属性修改表和/或索引。 直接路径并行加载的格式样例： /opt/app/oracle/product/10.2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTLdirect=true parallel=true LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000 readsize=20000000 bindsize=20000000 DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis
从oracle导出数据
把要执行的sql语句放入文件test.sql中，比如select * from dayinsert; 把如下代码放入testexport.sql中 ------------------------------------------------- set echo off; set feedback off; set verify off; set term off; set trimspool on; set linesize 3000; --set pagesize 999; set newpage none; set heading off; spool resultexport.txt; ##输出结果的文件名字，可自定义 @test.sql; ##要执行的sql语句，本例为test.sql spool off; --------------------------------------------- 建立运行脚本run.sh, 放入如下代码 nohup sqlplus "user_name/password"@service_name @testexport.sql &
#######################################################
1 source profile 不成功，可能是bash没有起来，输入bash，启动bash命令；
2 怎么从一个大文件筛选出符合要求的条目；
cat ./sample.txt|while read line do cat ./source.txt |grep "$line" >> result.txt done
第一次导入数据的经历，写下来留待以后查看。。。。
oracle数据表迁移 IP1 迁移到IP2 可用命令或客户端导出dmp文件 在IP2上新建和IP1一样的表空间名字 导入 imp user/password@DB file=out.dmp log=**.log fromuser=user touser=user1 buffer=409600
参考资料：
1 shell 拆分文件 http://blog.sina.com.cn/s/blog_551d7bff0100szyt.html
（完）
