SQL*Loader 的使用sqlldr和sqluldr2方法详解
oracle数据导出工具sqluldr2可以将数据以csv、txt等格式导出,适用于大批量数据的导出,导出速度非常快。
导出后可以使用oracle loader工具将数据导入。
简介:
Sqluldr2:专业用于大数据量导出工具之一,效率比普通导出快70%。
Sqlldr: 专业用于导入的工具之一, 在使用时,最好用磁盘写入速度快,网络好,网速快的做。
导出数据:
sqluldr2 user=c##scott/tiger@192.168.1.250/orcl field=0x03 sql=lead_table.sql file=lead_table.dat charset=ZHS16GBK head=no safe=yes fast=yes serial=ture
[oracle@master2 test]$ more lead_table.sql
select CASEID,STEPID,ACTIONDATE from LEAD_TABLE;
生成文件
[oracle@master2 test]$ sqluldr2 user=c##scott/tiger@192.168.1.250/orcl field=0x03 sql=lead_table.sql file=lead_table.dat charset=ZHS16GBK head=no safe=yes fast=yes serial=ture
0 rows exported at 2019-01-23 11:15:10, size 0 MB.
10 rows exported at 2019-01-23 11:15:10, size 0 MB.
output file lead_table.dat closed at 10 rows, size 0 MB.
导入数据:
[oracle@master2 test]$ sqlldr userid=c##scott/tiger@192.168.1.250/orcl data=lead_table.dat control=lead_table.ctl log=lead_table.log skip=0 rows=10000 direct=true streamsize=30720000 date_cache=30720000 SQL*Loader: Release 12.1.0.2.0 - Production on Wed Jan 23 11:50:18 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Path used: Direct Load completed - logical record count 1. Table LEAD_TABLE: 1 Row successfully loaded. Check the log file: lead_table.log for more information about the load. [oracle@master2 test]$ echo $? 0
控制文件:
[oracle@master2 test]$ more lead_table.ctl LOAD DATA CHARACTERSET 'ZHS16GBK' INFILE * INTO TABLE LEAD_TABLE FIELDS TERMINATED BY X'03' optionally enclosed by '"' trailing nullcols ( CASEID, STEPID, ACTIONDATE "to_timestamp(:ACTIONDATE,'YYYY-MM-DD-HH24:MI:SS.FF')" )