« SQLULDR2 : Parallel Inside Now! »
Tools » http://www.anysql.net/tools/parallel-inside-sqluldr2.html 2010-04-18通过的TabSplit程序的测试, 及完成并行版DataCopy工具后, 认为可以将TabSplit和SQLULDR2的源代码进行整合, 推出Parallel Inside的SQLULDR2程序, 使之成为导出海量Oracle数据到文本的利器.
通过指定并行度(DEGREE)参数即可进行并行数据拷贝, 在文件名中用%p来表示进程序号, 以获得动态文件名.
sqluldr2 scott/tiger query=emp_his file=emp_his_%p.txt degree=2
用700万数据的一个表进行测试, 并行方式执行时屏幕输出如下.
0 rows exported at 2010-04-18 07:16:49, size 0 MB.
0 rows exported at 2010-04-18 07:16:49, size 0 MB.
1000000 rows exported at 2010-04-18 07:17:00, size 52 MB.
1000000 rows exported at 2010-04-18 07:17:01, size 52 MB.
2000000 rows exported at 2010-04-18 07:17:11, size 104 MB.
2000000 rows exported at 2010-04-18 07:17:13, size 104 MB.
3000000 rows exported at 2010-04-18 07:17:22, size 156 MB.
3000000 rows exported at 2010-04-18 07:17:25, size 160 MB.
3570892 rows exported at 2010-04-18 07:17:28, size 189 MB.
output file emp_his_2.txt closed at 3570892 rows, size 189 MB.
3769140 rows exported at 2010-04-18 07:17:31, size 201 MB.
output file emp_his_1.txt closed at 3769140 rows, size 201 MB.
由于需要查询一些系统视图来自动对大表进行切分, 需要授于源端用户查询系统视图的权限(SELECT CATALOG ROLE角色)即可, 下周一上传后就可以下载进行性能测试了.
最好提供一个与串行的对比
http://www.anysql.net/tools/sqluldr_comming.html
23楼的需求能否加入？
能否实现
sqluldr2 scott/tiger query=emp1,emp2和
sqluldr2 scott/tiger query=emp partiton p1,p2
将SQLULDR2的并行用法改一下, 以求更灵活的并行功能, 用split参数指定并行的表, 用degree指定并行度.
在query中用%1表示小的ROWID, 用%2表示大的ROWID.
sqluldr2 scott/tiger query=”select * from emp_his where rowid>=’%1′and rowid<‘%2′” split=emp_his degree=2
没有并行
D:\app\instantclient10_1>sqluldr2 LT/LT@xxxx/orcl query=T file=emp_his_%p.txt degree=2
0 rows exported at 2010-04-19 12:28:57, size 0 MB.
1000000 rows exported at 2010-04-19 12:29:01, size 16 MB.
output file emp_his_1.txt closed at 1000000 rows, size 17 MB.
D:\app\instantclient10_1>sqluldr2 LT/LT@xxxx/orcl split=T file=emp_his_%p.txt degree=2
SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 – 2010, all rights reserved.
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).
parfile = read command option from parameter file
请看一下, 第四个回复, 为了适应更复杂的SQL的并行, 改了一下用法.
用户怎么知道rowid是什么，体验不好
拷贝了2份，真不会用
D:\app\instantclient10_1>sqluldr2 LT/LT@xxxx/orcl query=”select * from t where rowid>0″ split=T file=emp_his_%p.txt degree=2
ORA-00932: inconsistent datatypes: expected ROWID got NUMBER
ORA-00932: inconsistent datatypes: expected ROWID got NUMBER
D:\app\instantclient10_1>sqluldr2 LT/LT@xxxx/orcl query=”select * from t” split=T file=emp_his_%p.txt degree=2
0 rows exported at 2010-04-19 14:08:35, size 0 MB.
0 rows exported at 2010-04-19 14:08:35, size 0 MB.
1000000 rows exported at 2010-04-19 14:08:39, size 16 MB.
output file emp_his_1.txt closed at 1000000 rows, size 17 MB.
1000000 rows exported at 2010-04-19 14:08:39, size 16 MB.
output file emp_his_2.txt closed at 1000000 rows, size 17 MB.
嗯，有个问题，这个工具现在能支持多RAC节点并行导出吗？毕竟大部分海量数据库都是多节点的。我想如果能实现这一步，就能和data dump相媲美了，呵呵
如果你提供的连接可以自动进行LOAD BALANCE, 那么以并行方式导出时, 也可以说是从多个RAC节点并行导出的.
谢谢，也就是说在客户端会启动多个sqluldr实例，分别连到不同的DB instance上。然后数据库后台进程通过网络把数据返回客户端，再由客户端的sqluldr实例写入到目标文件？我开始以为是在多个服务器上启动多个sqluldr实例，这样也许会节省下从服务器到sqluldr机器所产生的网络流量。
呵呵，不错的工具，很高兴和你交流。
并行的使用方法又有改变, 详细请看.
http://www.dbatools.net/mytools/parallel-inside-sqluldr2.html