Sqluldr2 简单介绍 ： 简介： Sqluldr2 ：专业用于大数据量导出工具之一，效率比普通导出快70%。 ( Sqlldr ：专业用于导入的工具之一，请注意两个工具的区别。),在使用时,最好用磁盘写入速度快，网络好，网速快的做。 内部实现 ： [sql] view plaincopy #inc
Sqluldr2
简单介绍 ：
简介：
Sqluldr2 ：专业用于大数据量导出工具之一，效率比普通导出快70%。 ( Sqlldr ：专业用于导入的工具之一，请注意两个工具的区别。),在使用时,最好用磁盘写入速度快，网络好，网速快的做。
内部实现 ：
[sql] view plaincopy
#include #include "sqluldr2.h" void main() { void *h = NULL ; SQLULDR2HandleAlloc(&h); if(h != NULL ) { SQLULDR2HandleSetAttr(h, "USER=SYS" ); SQLULDR2HandleSetAttr(h, "QUERY=SELECT * FROM TAB" ); SQLULDR2HandleExecute(h); SQLULDR2HandleFree(h); } }
工具列表:
针对不同平台用不同的导出工具：
Windows: sqluldr2.exe
Linux(32位)： sqluldr2_linux32_10204.bin
Linux(64位): sqluldr2_linux64_10204.bin
使用说明 （ Windows平台 ）：
使用sqluldr2的步骤：
1.打开运行àcmd进入到sqluldr2.exe的当前目录
2.参数介绍
User=用户/密码@tns
Query=”查询语句”
File= 导出的路径
Head= 输出信息时，yes表示要表头，no表示不要表头
注意：想查看更多参数，请输入 sqluldr2 help=yes
[sql] view plaincopy
C:\Users\meng\Desktop\sqluldr2>sqluldr2.exe help=yes SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1 (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved. License: Free for non-commercial useage, else 100 USD per server. Usage: SQLULDR2 keyword=value [,keyword=value,...] Valid Keywords: user = username/ password @tnsname sql = SQL file name query = select statement field = separator string between fields record = separator string between records rows = print progress for every given rows ( default , 1000000) file = output file name ( default : uldrdata.txt) log = log file name , prefix with + to append mode fast = auto tuning the session level parameters(YES) text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH). charset = character set name of the target database . ncharset= national character set name of the target database . parfile = read command option from parameter file read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level sort = set SORT_AREA_SIZE at session level (UNIT:MB) hash = set HASH_AREA_SIZE at session level (UNIT:MB) array = array fetch size head = print row header(Yes| No ) batch = save to new file for every rows batch (Yes/ No ) size = maximum output file piece size (UNIB:MB) serial = set _serial_direct_read to TRUE at session level trace = set event 10046 to given level at session level table = table name in the sqlldr control file control = sqlldr control file and path. mode = sqlldr option , INSERT or APPEND or REPLACE or TRUNCATE buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB) long = maximum long field size width = customized max column width (w1:w2:...) quote = optional quote string data = disable real data unload ( NO , OFF ) alter = alter session SQLs to be execute before unload safe = use large buffer to avoid ORA-24345 error (Yes| No ) crypt = encrypted user information only (Yes| No ) sedf/t = enable character translation function null = replace null with given value escape = escape character for special characters escf/t = escape from / to characters list format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs. exec = the command to execute the SQLs. prehead = column name prefix for head line. rowpre = row prefix string for each line. rowsuf = row sufix string for each line. colsep = separator string between column name and value. presql = SQL or scripts to be executed before data unload. postsql = SQL or scripts to be executed after data unload. lob = extract lob values to single file (FILE). lobdir = subdirectory count to store lob files . split = table name for automatically parallelization. degree = parallelize data copy degree (2-128). hint = MySQL SQL hint for the Insert , for example IGNORE . unique = Unique Column List for the MySQL target table . update = Enable MySQL ON DUPLICATE SQL statement(YES/ NO ). for field and record, you can use '0x' to specify hex character code, \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27 C:\Users\meng\Desktop\sqluldr2>
代码例子1 ：
sqluldr2.exe USER=用户/ 密码 @ tns QUERY="select /*+ parallel(8) */ *from cs_XXX dt,cfg_XXX devdim105 wheredt.starttime>=to_date('2012-06-27 0','yyyy-mm-dd hh24:mi:ss') and dt.starttime
代码例子2( 这种方式用于导出的查询sql很长，那么就把sql写在123.sql文件里 )：
sqluldr2.exe USER=用户/ 密码 @ tns sql=123.sqlhead=yes FILE=F:\cs_XXX_test.csv
3.在cmd里，直接把步骤2的 代码例子1 ，贴进去执行。
注：如果执行报报错，就有可能是环境变量path 的问题，还有就是plsql需要的oci.dll文件等多个dll文件，请放在sqluldr2的目录下。
4.查看结果F:\cs_XXX_test.csv
5.打开csv里面的内容，就可以用sqlldr进行 入库。