联系:手机/微信(+86 17813235971) QQ(107644445)
标题:oracle之数据导入
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1、inset into select
ALTER TABLE EMP NOLOGGING;--为了使插入时少产生log记录,要修改表,不能直接加nologging
1.1、单线程插入
INSERT /*+ APPEND */ INTO TEST SELECT * FROM B.TEST; --加上/*+ APPEND */要比不加这个速度快的多,因为这个直接导入到高水位之后,而且是直接块对块的插入,不用经过复杂的sql转换
1.2、多线程插入
--创建分区表 create table emp (empno number, ename varchar2(20)) partition by hash(empno) (partition part1, partition part2); --执行多线程插入 alter session enable parallel dml; insert /*+ PARALLEL(emp,2) */ into emp select OBJECT_ID,TIMESTAMP from all_objects;
2、sql loader
上次服务器被攻击,我导入日志文件使用的是c#程序导入到oracle数据库中
这次使用sql loadr实现
control文件为
load data infile data.log "str '\n'" into table gj2 replace fields terminated by ' ' TRAILING NULLCOLS (a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14)
创建对应表
create table gj2(a1 varchar(10),a2 varchar2(10), a3 varchar2(100),a4 varchar2(100),a5 varchar2(100),a6 varchar2(100), a7 varchar2(200),a8 varchar2(100),a9 varchar2(100),a10 varchar2(100), a11 varchar2(200),a12 varchar2(100),a13 varchar2(100), a14 varchar2(100));
执行sql loader
sqlldr b/b control=c:\abc.txt log=log.tx
这个非常方便,很快就把几十万条记录导入到数据库中了,而且直接提出a1和a2列进行分析就可以得到上次通过程序导入到数据库,然后再通过orcale的正则表达式功能选择出时间和ip地址,相比之下这个简单多了,很好用。
原始数据:
2010-07-02 10:55:02 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 221.232.180.193 - 403 1 0 2010-07-02 10:55:04 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 121.35.96.46 - 403 1 0 2010-07-02 10:55:07 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 119.178.8.242 - 403 1 0 2010-07-02 10:55:13 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 124.236.72.63 - 403 1 0 2010-07-02 10:55:13 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 58.60.253.104 - 403 1 0 2010-07-02 10:55:14 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 120.10.3.64 - 403 1 0 2010-07-02 10:55:18 W3SVC689347672 192.168.10.222 HEAD /index.shtml - 80 - 221.196.165.225 - 403 1 0 2010-07-02 10:55:21 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 116.16.159.224 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:21 W3SVC689347672 192.168.10.222 GET /anlewwwroot.zip - 80 - 120.84.23.2 Mozilla/4.0 404 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 122.226.148.190 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 122.226.148.190 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 121.204.33.120 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 202.30.113.101 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 58.101.51.214 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 114.41.216.107 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 58.101.51.214 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64 2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 - 114.41.216.107 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
使用sql loader处理后结果:
以前使用c#导入,oracle正则表达式处理日志为:
服务器遭攻击,oracle分析日志