oracle導出多CSV檔案的靠譜的

oracle導出多CSV檔案的問題

----------------------------------------------------------------------

用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行換一個檔案名。

大家有什麼好方法嗎？注意效率

先一次性生成一個csv檔案

再用split分割檔案

--------------------------------------------------------

完全贊同樓上說的。

關于性能問題，用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

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/null

split -l 4000 -a 5 ${SqlDir}/EXPORT_ALL.CSV log_20060606_

=============================================================

以上是我的腳本，80w資料我用了3個多小時還沒完呢。有問題嗎？

另外，很重要的一點，split後，再不是csv字尾了。看來還得檔案周遊，然後一個一個的mv 成csv檔案。

先整個導出罷，然後在處理

你的代碼看起來沒有問題，但80萬條記錄導3小時沒完成很難讓人了解。

你用sqlplus做一下測試，比如導出10000條記錄需要多少時間，如果确實很慢，那可能是連接配接太慢（網絡速度？）。

導出為csv檔案很快，就幾分鐘而已，但是split要花3個小時，共250個檔案，每個檔案4000行。我的機器是SUN Blade 150，還有我在server上運作的，不存在網絡速度的問題。

根據需要導出的列，單獨建個臨時表，比如：create table sales_range_tmp as select salesman_id,salesman_name,sales_amount,sales_date from sales;

再利用腳本導出來，看看速度應該有所提高。不過有一點必須明白，全掃描8w的表資料肯定不是幾分鐘可以完成的，何況要寫成實體檔案。

KingSunSha(弱水三千),我用spool導出的檔案大小為9G(8900828448byte on solaris),而你的才80M

SELECT 中不要拼接字元串，非常影響速度

大家還有好的辦法嗎？

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 Bytes

select 中隻有 4 列（SALESMAN_ID, SALESMAN_NAME, SALES_AMOUNT, SALES_DATE

），你覺得每行紀錄占12KB合理嗎？

問題是你在sqlplus中用了set linesize=100000，是以每行都會spool出100000bytes。

解決這個問題很簡單，在spool開始之前加上兩行set指令

set trimspool on

set termout off

第一個set是指定trim spool出的每行在行末的空格

第二個set是指定隻spool而不回顯，是以在運作sqlplus的時候不需要 >/dev/null。

非常感謝KingSunSha(弱水三千),現在我把linesize改為100，非常快，2分鐘就完成了。太好了。不過還有一個問題，導出的檔案，每隔13行就會自動設定一個空行，能把這個去掉嗎？因為我導出的檔案會被我的程式打開的。

just add 

set pagesize 0

before spool to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.

just add

非常感謝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不支援。