0

I want to export 100 million rows in CSV File. I am using SQL DEVELOPER AND SQL CL but the fetching is taking so much time. I am using following SQL command in SQL CL.

SET FEEDBACK OFF
SET SQLFORMAT CSV
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
ALTER SESSION SET NLS_DATE_FORMAT = "YYYY-MM-DD";
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF TZR";

SPOOL C:\WORK\emp.csv
SELECT /*+PARALLEL*/ *FROM demo_table c;

Could anyone please help me how to export csv file faster?

Tomer Shetah
  • 8,413
  • 7
  • 27
  • 35
Kalyan Purohit
  • 1
  • 1
    Split the query up in batches, and run the exports in parallel.. Easy if the table is partitioned. – thatjeffsmith Dec 24 '20 at 13:28
  • Could you please explain it via example? – Kalyan Purohit Dec 24 '20 at 15:51
  • Don't get all 100m rows at once, run 5 copies of sqlcl getting 20m rows each. – thatjeffsmith Dec 24 '20 at 15:52
  • What are you going to do with a 100 million row CSV file? Enquiring minds want to know. – APC Dec 24 '20 at 16:59
  • I need to upload it to Snowflake as a temporary basis. Our IT department is waiting to get approval of ETL tables from Oralce to Snowflake and I need it urgently. Therefore, I need to copy table one time into Snowlfake. Once It department will take care of uploading this table, I will delete it. – Kalyan Purohit Dec 24 '20 at 17:12
  • I may be reading too much into your last comment but you seem to be saying that rather than wait for IT to get approval, you are going to bypass all you company's controls and load a huge amount of data into a database without approval. This seems like a good way to get instantly dismissed from your company - apart from the significant risk you are exposing your company to – NickW Dec 26 '20 at 08:47

0 Answers0