Export table / view to .csv in batches of x number of rows

user_9806365 0
Viewed 406

The server is an Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

I have about many tables / views of varying size (1 to 25 million rows x 5 to 100 columns).

I can only access the server through Oracle SQL Developer.

Due to limitation on the server hardware I'm not able to extract all rows in a single export using the "Export Wizard" so instead I'm splitting every table up into 'chunks' of 1 million using the following simply query:

SELECT * 
FROM [my_table]
OFFSET [rows_extracted_already] ROWS
FETCH NEXT 1000000 ROWS ONLY;

Then I get a Query Result -> Right-click -> Export -> Use the Export Wizard -> Repeat.

This is taking a LONG time and I cannot seem to find a simple way to do this.

oracle-sqldeveloper oracle sql
2 Answers

You need an "Order BY" clause that uses a unique index to speed things up. Without "ORDER BY" the rows are randomly selected, I'm not certain what you would get. The unique index will allow the query to quickly jump to the start of the next set of records without having to do a full table scan.

user_28004020

I have created a utility run_export_dir_tables.ps1 to export tables to csv files. The utility reads all files from the sql directory and exports to csv files. The utility uses the sqlplus utility, which achieves high performance.
The utility is written in powershell and you can configure a number of parameters through variables.

# Column separator for csv file 
$COLSEP=";"
# NLS_NUMERIC_CHARACTERS
$NLS_NUMERIC_CHARACTERS=".,"
$NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"
#csv file extension
$csv_ext=".csv"
#Set NLS_LANG for session sqlplus 
$NLS_LANG="AMERICAN_AMERICA.CL8MSWIN1251"
#$NLS_LANG="RUSSIAN_CIS.CL8MSWIN1251"
#$NLS_LANG="AMERICAN_AMERICA.UTF8"
user_77347320
Related