在oracle中,当要导出表的数据时,由于数据量巨大,导出的工作显得有点困难,现提供有如下几种方式:
1、imp/exp工具,导出来的是dmp文件,如果想要excel或txt就显得有点难;
2、PLSQL Developer开发工具,它也能实现导入数据,而且可以导出不同的格式,但是在导出数据的行数会存在一定的问题,一般在70-80万行还算正常,上百万的时候就可能有问题;
3、用spool,这是一种好方法;在使用时可以选择不将结是显示在屏幕,这就是同时要加上set term off,而且将语句放入SQL文件,以@run.sql方式运行;
4、网上的一个工具ociuldr,但它只导出为txt文件,这个工具速度超快。
SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.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).
charset = character set name of the target database.
ncharset= national character set name of the target database.
parfile = read command option from parameter file
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
E:\music\ociuldr>sqluldr2 user=music/iwie_0925bmc@dbserver query="select t.mbno
from t_sp_inbox t where t.intime>to_date('20100401', 'yyyymmdd') and (t.msg lik
e 'VV1%' or t.msg like 'VV2%')" field=, record=0x0a file=E:\music\kkk.txt
0 rows exported at 2010-10-11 09:38:42, size 0 MB.
1000000 rows exported at 2010-10-11 09:38:46, size 8 MB.
1657868 rows exported at 2010-10-11 09:38:49, size 19 MB.
output file E:\music\kkk.txt closed at 1657868 rows, size 19 MB.
D:\ociuldr>
------------End---------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9932141/viewspace-677387/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9932141/viewspace-677387/