Sqluldr2 简单介绍 ： 简介： Sqluldr2 ：专业用于大数据量导出工具之一，效率比普通导出快70%。 ( Sqlldr ：专业用于导入的工具之一，请注意两个工具的区别。),在使用时,最好用磁盘写入速度快，网络好，网速快的做。 内部实现 ： [sql] view plaincopy #inc

Sqluldr2

简单介绍：

简介：

Sqluldr2：专业用于大数据量导出工具之一，效率比普通导出快70%。 ( Sqlldr：专业用于导入的工具之一，请注意两个工具的区别。),在使用时,最好用磁盘写入速度快，网络好，网速快的做。

内部实现：

[sql] view plaincopy

1. #include
2. #include "sqluldr2.h"
4. void main()
5. {
6. void *h = NULL;
7. SQLULDR2HandleAlloc(&h);
8. if(h != NULL)
9. {
10. SQLULDR2HandleSetAttr(h, "USER=SYS");
11. SQLULDR2HandleSetAttr(h, "QUERY=SELECT * FROM TAB");
12. SQLULDR2HandleExecute(h);
13. SQLULDR2HandleFree(h);
14. }
15. }

工具列表:

针对不同平台用不同的导出工具：

Windows: sqluldr2.exe

Linux(32位)： sqluldr2_linux32_10204.bin

Linux(位): sqluldr2_linux_10204.bin

使用说明 （Windows平台）：

使用sqluldr2的步骤：

1.打开运行àcmd进入到sqluldr2.exe的当前目录

2.参数介绍

User=用户/密码@tns

Query=”查询语句”

File= 导出的路径

Head= 输出信息时，yes表示要表头，no表示不要表头

注意：想查看更多参数，请输入 sqluldr2 help=yes

[sql] view plaincopy

1. C:\Users\meng\Desktop\sqluldr2>sqluldr2.exe help=yes
4. SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
5. (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
8. License: Free for non-commercial useage, else 100 USD per server.
11. Usage: SQLULDR2 keyword=value [,keyword=value,...]
14. Valid Keywords:
15. user = username/password@tnsname
16. sql = SQL file name
17. query = select statement
18. field = separator string between fields
19. record = separator string between records
20. rows = print progress for every given rows (default, 1000000)
21. file = output file name(default: uldrdata.txt)
22. log = log file name, prefix with + to append mode
23. fast = auto tuning the session level parameters(YES)
24. text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
25. charset = character set name of the target database.
26. ncharset= national character set name of the target database.
27. parfile = read command option from parameter file
28. read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
29. sort = set SORT_AREA_SIZE at session level (UNIT:MB)
30. hash = set HASH_AREA_SIZE at session level (UNIT:MB)
31. array = array fetch size
32. head = print row header(Yes|No)
33. batch = save to new file for every rows batch (Yes/No)
34. size = maximum output file piece size (UNIB:MB)
35. serial = set _serial_direct_read to TRUE at session level
36. trace = set event 10046 to given level at session level
37. table = table name in the sqlldr control file
38. control = sqlldr control file and path.
39. mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE
40. buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB)
41. long = maximum long field size
42. width = customized max column width (w1:w2:...)
43. quote = optional quote string
44. data = disable real data unload (NO, OFF)
45. alter = alter session SQLs to be execute before unload
46. safe = use large buffer to avoid ORA-24345 error (Yes|No)
47. crypt = encrypted user information only (Yes|No)
48. sedf/t = enable character translation function
49. null = replace null with given value
50. escape = escape character for special characters
51. escf/t = escape from/to characters list
52. format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.
53. exec = the command to execute the SQLs.
54. prehead = column name prefix for head line.
55. rowpre = row prefix string for each line.
56. rowsuf = row sufix string for each line.
57. colsep = separator string between column name and value.
58. presql = SQL or scripts to be executed before data unload.
59. postsql = SQL or scripts to be executed after data unload.
60. lob = extract lob values to single file (FILE).
61. lobdir = subdirectory count to store lob files .
62. split = table name for automatically parallelization.
63. degree = parallelize data copy degree (2-128).
64. hint = MySQL SQL hint for the Insert, for example IGNORE.
65. unique = Unique Column List for the MySQL target table.
66. update = Enable MySQL ON DUPLICATE SQL statement(YES/NO).
69. for field and record, you can use '0x' to specify hex character code,
70. \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27
72. C:\Users\meng\Desktop\sqluldr2>

代码例子1：

sqluldr2.exe USER=用户/密码@tnsQUERY="select /*+ parallel(8) */ *from cs_XXX dt,cfg_XXX devdim105 wheredt.starttime>=to_date('2012-06-27 00:00:00','yyyy-mm-dd hh24:mi:ss') and dt.starttime<=to_date('2012-06-2700:01:59','yyyy-mm-dd hh24:mi:ss') AND dt.msc= devdim105.mapvalue(+) ANDdevdim105.deviceid=15 " head=yes FILE=F:\cs_XXX_test.csv

代码例子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进行 入库。