spool c:\temp\testskript_spool.sql
set trimspool on
conn / as sysdba
create or replace directory extern as 'c:\temp';
grant read, write on directory extern to scott;
DROP TABLE scott.big_tab PURGE;

CREATE TABLE scott.big_tab AS 
SELECT ROWNUM id,  owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary
FROM dba_objects a WHERE 1=0;
ALTER TABLE big_tab NOLOGGING;

DECLARE
  l_cnt  NUMBER;
  l_rows NUMBER := 2000000;
BEGIN
  INSERT /*+ APPEND */ INTO scott.big_tab
  SELECT ROWNUM, owner, object_name, subobject_name, object_id,  
        data_object_id, object_type, created, last_ddl_time, timestamp,
        status, temporary, generated, secondary
  FROM dba_objects;
  l_cnt := SQL%ROWCOUNT;
  COMMIT;
  WHILE l_cnt < l_rows LOOP
    INSERT /*+ APPEND */ INTO scott.big_tab 
    SELECT ROWNUM + l_cnt, owner, object_name, subobject_name, object_id,
           data_object_id, object_type, created, last_ddl_time, timestamp,
           status, temporary, generated, secondary
    FROM scott.big_tab
    WHERE  ROWNUM <= l_rows - l_cnt;
    l_cnt  := l_cnt + SQL%ROWCOUNT;
  COMMIT;
  END LOOP;
END;
/

ALTER TABLE scott.big_tab ADD CONSTRAINT big_tab_pk PRIMARY KEY(id);
--I gathered baseline statistics on the table and the index associated with the primary key. 
--Additionally, I gathered histograms on the indexed column (something I typically do). 
--Histograms may be gathered on other columns as well, but for this table, it just isn't necessary.
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
             ownname => 'SCOTT', 
             tabname => 'BIG_TAB', 
          method_opt => 'for all indexed columns', 
    estimate_percent => 100,
             cascade => TRUE);
END;
/

conn scott/tiger

drop table csv_test1;
create table csv_test1(
num_col1   number(9) primary key,  
num_col2   number(9),
string_col1     varchar2(200),
string_col2     varchar2(200),
string_col3     varchar2(200),
string_col4     varchar2(200),
string_col5     varchar2(200),
string_col6     varchar2(200),
num_col3   number(5),
num_col4  number(5),
num_col5 number(1),
num_col6  number(9,6),
num_col7   number(3),
string_col7     varchar2(200),
string_col8     varchar2(200),
num_col8   number(11,3),
date_col1  date,
string_col9     varchar2(200),
num_col9  number(1),
num_col10  number(1),
num_col11  number(15,7),
num_col12  number(3),
date_col2   date,
num_col13   number(15,7),
string_col10     varchar2(200),
num_col14   number(1),
num_col15   number(1),
num_col16   number(3),
string_col11     varchar2(200),
num_col20   number(9),
date_col3   date,
date_col4   date,
char_col1  char(300),
num_col21   number(3),
num_col22   number(1),
char_col2  char(300),
date_col5   date,
date_col6   date,
date_col7   date,
date_col8   date,
string_col12     varchar2(200),
num_col27  number(11,2),
num_col29  number(17,5),
date_col9   date,
string_col13     varchar2(200),
char_col3  char(300),
char_col4  char(300),
string_col14     varchar2(200),
string_col15     varchar2(200));

drop sequence csv_test_seq;
create sequence csv_test_seq;
begin
  for i in 1.. 100000 loop
   insert into csv_test1 values(
    csv_test_seq.nextval ,
    dbms_random.value(1,9999999),
    rpad('Inhalt von string_col1: '||i,100, 'Bla'),
    rpad('Inhalt von string_col2: '||i,100, 'Bla'),
    rpad('Inhalt von string_col3: '||i,100, 'Bla'),
    rpad('Inhalt von string_col4: '||i,100, 'Bla'),
    rpad('Inhalt von string_col5: '||i,100, 'Bla'),
    rpad('Inhalt von string_col6: '||i,100, 'Bla'),
    dbms_random.value(1,99999),
    dbms_random.value(1,99999),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    dbms_random.value(1,999),
    rpad('Inhalt von string_col7: '||i,100, 'Bla'),
    rpad('Inhalt von string_col8: '||i,100, 'Bla'),
    dbms_random.value(1,99999999),
    sysdate - dbms_random.value,
    rpad('Inhalt von string_col9: '||i,100, 'Bla'),
    dbms_random.value(0,9),
    dbms_random.value(0,9),
    dbms_random.value(1,99999999),
    dbms_random.value(1,999),
    sysdate - dbms_random.value,
    dbms_random.value(1,99999999),
    rpad('Inhalt von string_col10: '||i,100, 'Bla'),
    dbms_random.value(0,9),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    rpad('Inhalt von string_col11: '||i,100, 'Bla'),
    dbms_random.value(1,999999),
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    rpad('Inhalt von char_col1: '||i,100, 'Bla'),
    dbms_random.value(1,999),
    dbms_random.value(0,9),
    rpad('Inhalt von char_col2: '||i,100, 'Bla'),
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    rpad('Inhalt von string_col12: '||i,100, 'Bla'),
    dbms_random.value(1,9999999),
    dbms_random.value(1,9999999),
    sysdate - dbms_random.value,
    rpad('Inhalt von string_col13: '||i,100, 'Bla'),
    rpad('Inhalt von char_col3: '||i,100, 'Bla'),
    rpad('Inhalt von char_col4: '||i,100, 'Bla'),
    rpad('Inhalt von string_col14: '||i,100, 'Bla'),
    rpad('Inhalt von string_col15: '||i,100, 'Bla'));
 end loop;
 end;
/
commit;

drop table csv_test2;
create table csv_test2(
num_col1   number(9) primary key,  
num_col2   number(9),
string_col1     varchar2(200),
string_col2     varchar2(200),
string_col3     varchar2(200),
string_col4     varchar2(200),
string_col5     varchar2(200),
string_col6     varchar2(200),
num_col3   number(1),
num_col4   number(3),
num_col5   number(3),
num_col6   number(5),
num_col7   number(5),
num_col8  number(1),
num_col9   number(9,6),
num_col10   number(3),
string_col7     varchar2(200),
string_col8     varchar2(200),
num_col11   number(11),
date_col1  date,
num_col12   number(3),
num_col13   number(1),
num_col14   number(11),
num_col15   number(3),
num_col16   number(11),
num_col17   number(3),
num_col18   number(3),
num_col19   number(1),
num_col20   number(3),
string_col9     varchar2(200),
num_col21   number(1),
num_col22   number(1),
num_col23   number(15,7),
num_col24   number(3),
date_col2   date,
num_col25   number(15,7),
string_col10     varchar2(200),
num_col26   number(1),
num_col27   number(1),
num_col28   number(3),
string_col11     varchar2(200),
num_col29   number(9),
date_col3   date,
num_col30   number(1),
num_col31   number(5),
num_col32   number(5,2),
num_col33   number(5,3),
num_col34   number(7,3),
date_col4   date,
num_col35   number(17,7),
num_col36   number(1),
num_col37   number(9,6),
num_col38   number(1),
char_col1  char(300),
num_col39   number(3),
num_col40   number(1),
char_col2  char(300),
num_col41   number(3),
num_col42   number(3),
num_col43   number(1) not null,
num_col44   number(1) not null,
date_col5   date,
date_col6   date,
date_col7   date,
date_col8   date,
num_col45   number(3),
string_col12     varchar2(200),
num_col46   number(11,2),
num_col47   number(3),
num_col48   number(17,5),
num_col49   number(3),
date_col9   date,
num_col50   number(1),
num_col51   number(3),
string_col13     varchar2(200),
num_col52   number(1),
num_col53   number(1),
char_col3  char(300),
char_col4  char(300),
string_col14     varchar2(200),
string_col15     varchar2(200));

drop sequence csv_test_seq;
create sequence csv_test_seq;
begin
  for i in 1.. 30000 loop
   insert into csv_test2 values(
    csv_test_seq.nextval ,
    dbms_random.value(1,9999999),
    rpad('Inhalt von string_col1: '||i,100, 'Bla'),
    rpad('Inhalt von string_col2: '||i,100, 'Bla'),
    rpad('Inhalt von string_col3: '||i,100, 'Bla'),
    rpad('Inhalt von string_col4: '||i,100, 'Bla'),
    rpad('Inhalt von string_col5: '||i,100, 'Bla'),
    rpad('Inhalt von string_col6: '||i,100, 'Bla'),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    dbms_random.value(1,999),
    dbms_random.value(1,99999),
    dbms_random.value(1,99999),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    dbms_random.value(1,999),
    rpad('Inhalt von string_col7: '||i,100, 'Bla'),
    rpad('Inhalt von string_col8: '||i,100, 'Bla'),
    dbms_random.value(1,99999999),
    sysdate - dbms_random.value,
    dbms_random.value(1,999),
    dbms_random.value(0,9),
    dbms_random.value(1,99999999),
    dbms_random.value(1,999),
    dbms_random.value(1,99999999),
    dbms_random.value(1,999),
    dbms_random.value(1,999),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    rpad('Inhalt von string_col9: '||i,100, 'Bla'),
    dbms_random.value(0,9),
    dbms_random.value(0,9),
    dbms_random.value(1,99999999),
    dbms_random.value(1,999),
    sysdate - dbms_random.value,
    dbms_random.value(1,99999999),
    rpad('Inhalt von string_col10: '||i,100, 'Bla'),
    dbms_random.value(0,9),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    rpad('Inhalt von string_col11: '||i,100, 'Bla'),
    dbms_random.value(1,999999),
    sysdate - dbms_random.value,
    dbms_random.value(0,9),
    dbms_random.value(1,99999),
    dbms_random.value(1,999),
    dbms_random.value(1,99),
    dbms_random.value(1,9999),
    sysdate - dbms_random.value,
    dbms_random.value(1,9999999),
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    dbms_random.value(0,9),
    rpad('Inhalt von char_col1: '||i,100, 'Bla'),
    dbms_random.value(1,999),
    dbms_random.value(0,9),
    rpad('Inhalt von char_col2: '||i,100, 'Bla'),
    dbms_random.value(1,999),
    dbms_random.value(1,999),
    dbms_random.value(1,9),
    dbms_random.value(1,9),
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    sysdate - dbms_random.value,
    dbms_random.value(1,999),
    rpad('Inhalt von string_col12: '||i,100, 'Bla'),
    dbms_random.value(1,9999999),
    dbms_random.value(1,999),
    dbms_random.value(1,9999999),
    dbms_random.value(1,999),
    sysdate - dbms_random.value,
    dbms_random.value(0,9),
    dbms_random.value(1,999),
    rpad('Inhalt von string_col13: '||i,100, 'Bla'),
    dbms_random.value(0,9),
    dbms_random.value(0,9),
    rpad('Inhalt von char_col3: '||i,100, 'Bla'),
    rpad('Inhalt von char_col4: '||i,100, 'Bla'),
    rpad('Inhalt von string_col14: '||i,100, 'Bla'),
    rpad('Inhalt von string_col15: '||i,100, 'Bla'));
 end loop;
 end;
/
commit;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
             ownname => 'SCOTT', 
             tabname => 'CSV_TEST1', 
          method_opt => 'for all indexed columns', 
    estimate_percent => 100,
             cascade => TRUE);
END;
/

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
             ownname => 'SCOTT', 
             tabname => 'CSV_TEST2', 
          method_opt => 'for all indexed columns', 
    estimate_percent => 100,
             cascade => TRUE);
END;
/

-- wir brauchen eine Funktion, um die Spaltenliste zu erstellen
-- Um bei der Ausgabe über UTL_FILE Platz zu sparen, können String-Spalten optional mit der TRIM-Funktion versehen werden
CREATE OR REPLACE FUNCTION col_list (
      p_tabname   VARCHAR2,
      p_schema     VARCHAR2 DEFAULT user,
      p_trim      NUMBER DEFAULT 0,
      p_delim     VARCHAR2 DEFAULT ',')  RETURN VARCHAR2
AS
    l_col_list VARCHAR2(4000) := ' ';
BEGIN
    FOR rec IN (SELECT column_name,data_type FROM all_tab_columns
              WHERE table_name = UPPER(p_tabname)
              AND owner        = UPPER(p_schema)
              ORDER BY column_id) LOOP
      IF p_trim = 1 AND rec.data_type IN ('CHAR', 'VARCHAR2') THEN
        l_col_list  := l_col_list ||p_delim||'TRIM('||rec.column_name||')';
      ELSE
        l_col_list := l_col_list ||p_delim||rec.column_name;
      END IF;
    END LOOP;
    RETURN NVL(LTRIM(l_col_list, p_delim||' '), 'ungültiger Tabellenname oder fehlende Berechtigung');
END;
/

-- und eine Prozedur für die Ausgabe des csv-Files
CREATE OR REPLACE PROCEDURE tab2csv (
   p_directory VARCHAR2,
   p_tabname   VARCHAR2,
   p_schema    VARCHAR2 DEFAULT USER)
IS
    tab_refcur        SYS_REFCURSOR;
    l_header          VARCHAR2(4000);
    l_zeile           VARCHAR2(4000);
    l_col_list        VARCHAR2(4000);
    l_stmt            VARCHAR2(4000);
    l_file            UTL_FILE.FILE_TYPE;
 BEGIN
  l_file := UTL_FILE.FOPEN(
     location  => UPPER(p_directory),
    filename  => p_tabname||'_'||TO_CHAR(sysdate,'yyyy-mm-dd-hh24-mi')||'.csv',
    open_mode => 'w',
    max_linesize => 32767);
    l_header := col_list(
            p_tabname => p_tabname,
              p_schema => p_schema,
              p_delim => ';');
   -- Der Header wird ins File geschrieben
  UTL_FILE.PUT_LINE(l_file, l_header);
    l_col_list := col_list(
             p_tabname => p_tabname,
               p_schema => p_schema ,
               p_delim => q'[||';'||]',
                p_trim => 1);
    l_stmt   := 'SELECT '||l_col_list||' FROM '||p_schema||'.'||p_tabname;
    OPEN tab_refcur FOR l_stmt;
    LOOP
       FETCH tab_refcur INTO l_zeile;
       EXIT WHEN tab_refcur%NOTFOUND;
       -- mit der Prozedur PUT_LINE wird Zeile für Zeile geschrieben
     UTL_FILE.PUT_LINE(l_file, l_zeile);
    END LOOP;
    CLOSE tab_refcur;
   UTL_FILE.FCLOSE(l_file);
EXCEPTION
 WHEN others THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
   UTL_FILE.FCLOSE(l_file);
END tab2csv;
/

-- Jetzt probieren wir mal die gepufferte Lösung von Adrian Billington aus
CREATE OR REPLACE PROCEDURE tab2csv_fast (
   p_directory VARCHAR2,
   p_tabname   VARCHAR2,
   p_schema    VARCHAR2 DEFAULT USER)
IS
    tab_refcur        SYS_REFCURSOR;
    l_header          VARCHAR2(4000);
    l_zeile           VARCHAR2(4000);
    l_col_list        VARCHAR2(4000);
    l_stmt            VARCHAR2(4000);
    l_file            UTL_FILE.FILE_TYPE;
    l_buffer          VARCHAR2(32767);
 BEGIN
  l_file := UTL_FILE.FOPEN(
        location => UPPER(p_directory),
        filename => p_tabname||'_'||TO_CHAR(sysdate,'yyyy-mm-dd-hh24-mi')||'.csv',
       open_mode => 'w',
    max_linesize => 32767);
    l_header := col_list(
            p_tabname => p_tabname,
              p_schema => p_schema,
              p_delim => ';');
   UTL_FILE.PUT_LINE(l_file, l_header);
    l_col_list := col_list(
             p_tabname => p_tabname,
               p_schema => p_schema ,
               p_delim => q'[||';'||]',
                p_trim => 1);
    l_stmt := 'SELECT '||l_col_list||' FROM '||p_schema||'.'||p_tabname;
    OPEN tab_refcur FOR l_stmt;
    LOOP
       FETCH tab_refcur INTO l_zeile;
       EXIT WHEN tab_refcur%NOTFOUND;
       IF LENGTH(l_buffer) + 1 + LENGTH(l_zeile) <= 32767 THEN
         l_buffer := l_buffer || CHR(10) ||l_zeile;
       ELSE
         IF l_buffer IS NOT NULL THEN
            UTL_FILE.PUT_LINE(l_file, l_buffer);
         END IF;
         l_buffer := l_zeile;
       END IF;
    END LOOP;
    UTL_FILE.PUT_LINE(l_file, l_buffer);
    CLOSE tab_refcur;
   UTL_FILE.FCLOSE(l_file);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
   UTL_FILE.FCLOSE(l_file);
END tab2csv_fast;
/

CREATE TYPE num_nt_type AS TABLE OF NUMBER;
/

CREATE SEQUENCE file_seq;

CREATE OR REPLACE FUNCTION parallel_output (
      p_refcur    SYS_REFCURSOR,
      p_directory VARCHAR2,
      p_tabname   VARCHAR2,
      p_schema    VARCHAR2) RETURN num_nt_type PIPELINED
PARALLEL_ENABLE (PARTITION p_refcur BY ANY)
AS
   TYPE string_nt_type IS TABLE OF VARCHAR2(32767);
   string_nt    string_nt_type;
    l_name       VARCHAR2(200);
    l_header     VARCHAR2(4000);
    l_zeile      VARCHAR2(4000);
    l_col_list   VARCHAR2(4000);
    l_stmt       VARCHAR2(4000);
    l_file       UTL_FILE.FILE_TYPE;
    l_buffer     VARCHAR2(32676);
BEGIN
   l_name := p_tabname||'_'||TO_CHAR(sysdate,'yyyy-mm-dd-hh24-mi');
   l_file := UTL_FILE.FOPEN(
        location => UPPER(p_directory),
        filename => l_name||'.csv',
       open_mode => 'w',
    max_linesize => 32767);
    l_header := col_list(
            p_tabname => p_tabname,
              p_schema => p_schema,
              p_delim => ';');
   UTL_FILE.PUT_LINE(l_file, l_header);
   l_name := l_name|| '_' ||file_seq.NEXTVAL|| '.csv';
   l_file := UTL_FILE.FOPEN(
        location => UPPER(p_directory),
        filename => l_name,
       open_mode => 'w',
    max_linesize => 32767);
    LOOP
     FETCH p_refcur BULK COLLECT INTO string_nt LIMIT 100;
     EXIT WHEN string_nt.COUNT = 0;
      FOR i IN 1 .. string_nt.COUNT LOOP
         IF LENGTH(l_buffer) + 1 + LENGTH(string_nt(i)) <= 32676 THEN
            l_buffer := l_buffer ||CHR(10)|| string_nt(i);
         ELSE
            IF l_buffer IS NOT NULL THEN
               UTL_FILE.PUT_LINE(l_file, l_buffer);
            END IF;
            l_buffer := string_nt(i);
         END IF;
      END LOOP;
   END LOOP;
   CLOSE p_refcur;
   UTL_FILE.PUT_LINE(l_file, l_buffer);
   UTL_FILE.FCLOSE(l_file);
   PIPE ROW (file_seq.NEXTVAL);
   RETURN;
 EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
   UTL_FILE.FCLOSE(l_file);
END parallel_output;
/

CREATE OR REPLACE PROCEDURE tab2csv_parallel(
    p_directory VARCHAR2,
    p_tabname   VARCHAR2,
    p_schema    VARCHAR2 DEFAULT USER)
AS
type num_nt_type IS  TABLE OF NUMBER;
  num_nt num_nt_type;
  l_stmt     VARCHAR2(5000);
  l_col_list VARCHAR2(5000);
BEGIN
  l_col_list := col_list(p_tabname, p_schema, 1, '||'';''||');
  l_stmt   := ' SELECT * FROM TABLE(parallel_output( 
               p_refcur => CURSOR(SELECT /*+ PARALLEL(t,4) */ '||l_col_list||' FROM '||p_tabname||' t),'|| 
               q'[p_directory => ']'||upper(p_directory)|| q'[', p_tabname => ']'||p_tabname||q'['))]';
  DBMS_OUTPUT.PUT_LINE(l_stmt);
  EXECUTE IMMEDIATE l_stmt BULK COLLECT INTO num_nt;
END;
/