Oracle exports multi-CSV files in a reliable

Problems with Oracle exporting multi-CSV files

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

Using the Ksh script to export 80w data to a CSV file from an Oracle database, such as a user given a name of A.CSV (maximum 4000 rows of files), the automated production file is A_1.csv,a_2.csv,...., a_200.csv

I have implemented a method, but the 80w will lead 5 hours, the user cannot accept. As follows:
Sqlplus-s user/pwd @${sqldir}/select_log.sql >/tmp/a.tmp
Then each read 4000 line to the file a_1.csv, and every 4000 lines for a file name.

Do you have any good ways? Pay attention to efficiency

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

Generate a CSV file at once
Split the file again with split

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

Fully agree with what is said upstairs.

On performance issues, using Oracle spool out 800,000 lines of records is very fast, I just tried, and then HP-UX, spool out of a table 1 million records (2 columns) takes about 2 minutes, output file 80m, split with split command for each 4000 lines of the file takes a few seconds. The test code is attached:

/* 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_
=============================================================

The above is my script, 80w data I spent 3 hours not finished. Is there a problem?
Also, it's important that after split, it's not a CSV suffix anymore. It appears that the file is traversed and then one MV into a CSV file.



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

The entire export first, and then in the processing

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

Your code doesn't look like a problem, but it's hard to understand that 800,000 records are not finished in 3 hours.

You use Sqlplus to do a test, such as how much time to export 10,000 records, if it is really slow, it may be too slow connection (network speed?). ）。

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

Export to CSV file quickly, just for a few minutes, but split takes 3 hours, a total of 250 files, 4000 lines per file. My machine is Sun Blade 150, and I am running on the server, there is no network speed problem.

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

Create a separate temporary table for the columns you want to export, such as the created table sales_range_tmp as select Salesman_id,salesman_name,sales_amount,sales_date from Sales
Then use the script to export, to see how the speed should be increased. However, it must be understood that the full scan of the 8w table data is certainly not a few minutes to complete, let alone write a physical file.


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

Kingsunsha (weak water 3,000), the file size I exported with spool is 9G (8900828448byte on Solaris), and your only 80M

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

Do not stitch strings in SELECT, which greatly affects the speed

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

Do you have a good idea?

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

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

Only 4 columns in select (salesman_id, Salesman_name, Sales_Amount, sales_date
Do you think 12KB is reasonable for each record?

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

The problem is that you're using set linesize=100000 in Sqlplus, so each line will spool out 100000bytes.
It's easy to solve this problem by adding two lines of SET command before spool starts

Set Trimspool on
Set Termout off

The first set is the space at the end of each row of the specified trim spool
The second set specifies that only spool is not echoed, so >/dev/null is not required when running sqlplus.


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

Thank you very much Kingsunsha (weak water 3,000), now I change linesize to 100, very fast, 2 minutes to complete. That's great. However, there is a problem, the exported file, every 13 lines will automatically set a blank line, can you get rid of it? Because the file I exported will be opened by my program.

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

Just add
Set pagesize 0
Before spool to suppress-headings, page breaks, titles, the initial blank line, and other formatting information.


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

Just add
Set pagesize 0
Before spool to suppress-headings, page breaks, titles, the initial blank line, and other formatting information.

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

Thank you very much Kingsunsha (weak water 3,000), done, put it out, share!

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

800,010 minutes to get it done.

Ksh can be executed because there is this sentence: $ (ls./), SH is not supported.

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

Thank you!

http://blog.csdn.net/studyvcmfc/article/details/7047986

Oracle exports multi-CSV files in a reliable