oracle导出多CSV文件的问题

Question

用ksh脚本从oracle数据库中导出80w数据到csv文件，如用户给定名字为a.csv(文件最大4000行记录），则自动生产文件为a_1.csv,a_2.csv,...., a_200.csv我已经实现了一个方法，但80w要导5小时，用户没法接受。如下：
sqlplus -s user/pwd @${SqlDir}/select_log.sql >/tmp/a.tmp
然后每read 4000行到文件a_1.csv,并且每隔4000行换一个文件名。大家有什么好方法吗？注意效率

Answer 1

先一次性生成一个csv文件
再用split分割文件

Answer 2

完全赞同楼上说的。关于性能问题，用oracle spool出80万行纪录是非常快的，我刚试了一下，再hp-ux下，spool出某表中100万纪录（2列）耗时大约2分钟，输出文件80m, 用split命令分割为每个4000行的文件耗时数秒而已。 下附测试代码:/* sql file:  spool_test.sql */
set pages 0
set head off
set echo off
set feedback off
spool spool_text.csv
select col1, col2 from my_table where rownum<1000000;
spool off
exit## shell script to spool out:
sqlplus -s user/password @spool_test.sql > /dev/null## split file:
split -l 4000 spool_text.csv splited

Answer 3

export.sh#Generate the sql language
echo "SET HEADING OFF;">${SqlDir}/select_log.sql
echo "SET FEEDBACK OFF;">>${SqlDir}/select_log.sql
echo "SET LINESIZE 10000;">>${SqlDir}/select_log.sql
echo "SPOOL ${SqlDir}/EXPORT_ALL.CSV;" >> ${SqlDir}/select_log.sql
echo "SELECT SALESMAN_ID||','||">>${SqlDir}/select_log.sql
echo "SALESMAN_NAME||','||">>${SqlDir}/select_log.sql
echo "SALES_AMOUNT||','||">>${SqlDir}/select_log.sql
echo "SALES_DATE from sales_range;">>${SqlDir}/select_log.sql
echo "SPOOL OFF;" >> ${SqlDir}/select_log.sql
echo exit | ${ORACLE_HOME}/bin/sqlplus -s omc_sbardz812/omc_sbardz812 @${SqlDir}
/select_log.sql >/dev/nullsplit -l 4000 -a 5 ${SqlDir}/EXPORT_ALL.CSV log_20060606_
=============================================================以上是我的脚本，80w数据我用了3个多小时还没完呢。有问题吗？
另外，很重要的一点，split后，再不是csv后缀了。看来还得文件遍历，然后一个一个的mv 成csv文件。

Answer 4

你的代码看起来没有问题，但80万条记录导3小时没完成很难让人理解。你用sqlplus做一下测试，比如导出10000条记录需要多少时间，如果确实很慢，那可能是连接太慢（网络速度？）。

Answer 5

导出为csv文件很快，就几分钟而已，但是split要花3个小时，共250个文件，每个文件4000行。我的机器是SUN Blade 150，还有我在server上运行的，不存在网络速度的问题。

Answer 6

根据需要导出的列，单独建个临时表，比如：create table sales_range_tmp as select salesman_id,salesman_name,sales_amount,sales_date from sales;
再利用脚本导出来，看看速度应该有所提高。不过有一点必须明白，全扫描8w的表数据肯定不是几分钟可以完成的，何况要写成物理文件。

Answer 7

KingSunSha(弱水三千),我用spool导出的文件大小为9G(8900828448byte on solaris),而你的才80M

Answer 8

SELECT 中不要拼接字符串，非常影响速度

Answer 9

9GB = 9,216 MB = 9,437,184 KB = 9,663,676,416 Bytes
800,000 records
size of each record (line) = 9GB/800,000 = 12,079 Bytesselect 中只有 4 列（SALESMAN_ID, SALESMAN_NAME, SALES_AMOUNT, SALES_DATE
），你觉得每行纪录占12KB合理吗？

Answer 10

问题是你在sqlplus中用了set linesize=100000，所以每行都会spool出100000bytes。
解决这个问题很简单，在spool开始之前加上两行set命令set trimspool on
set termout off第一个set是指定trim spool出的每行在行末的空格
第二个set是指定只spool而不回显，所以在运行sqlplus的时候不需要 >/dev/null。

Answer 11

非常感谢KingSunSha(弱水三千),现在我把linesize改为100，非常快，2分钟就完成了。太好了。不过还有一个问题，导出的文件，每隔13行就会自动设置一个空行，能把这个去掉吗？因为我导出的文件会被我的程序打开的。

Answer 12

just add 
set pagesize 0
before spool to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

Answer 13

just add
set pagesize 0
before spool to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

Answer 14

非常感谢KingSunSha(弱水三千),搞定了，贴出来吧，共享！echo "SET HEADING OFF;">${SqlDir}/select_log.sql 
echo "SET FEEDBACK OFF;">>${SqlDir}/select_log.sql 
echo "SET LINESIZE 500;">>${SqlDir}/select_log.sql 
echo "SET trimspool on;">>${SqlDir}/select_log.sql 
echo "SET termout off;" >>${SqlDir}/select_log.sql 
echo "SET NEWPAGE NONE;" >>${SqlDir}/select_log.sql 
echo "SPOOL ${SqlDir}/EXPORT_ALL.CSV;" >> ${SqlDir}/select_log.sql 
echo "SELECT SALESMAN_ID||','||">>${SqlDir}/select_log.sql 
echo "SALESMAN_NAME||','||">>${SqlDir}/select_log.sql 
echo "SALES_AMOUNT||','||">>${SqlDir}/select_log.sql 
echo "SALES_DATE from sales_range;">>${SqlDir}/select_log.sql 
echo "SPOOL OFF;" >> ${SqlDir}/select_log.sql 
echo exit | ${ORACLE_HOME}/bin/sqlplus -s user/pwd @${SqlDir}/select_log.sql split -l 4000 -a 3 ${SqlDir}/EXPORT_ALL.CSV log_20060606_ #mv these splitted file to be csv file 
for file in $(ls ./) 
do 
    echo $file | grep log_20060606_ > /dev/null 
    status=$? 
    if [ $status -eq 0 ]; then 
        mv $file $file.csv 
    fi 
    status=1 
done 80万10分钟就可以搞定了。ksh可以执行，由于有这句：$(ls ./),sh不支持。