Export with Spool and Parallel Utl_File

Dump with SPOOL
set trimspool on --removes trailing blanks at the end of each displayed or spooled line.
set feedback off --Do not display the number of records returned by a query
set termout off  --Do not display the output generated by commands executed from a script.
set pages 0 --to suppress all headings, page breaks, titles, the initial blank line, and other formatting information.
set echo off --Suppresses the display of echo commands in a script that is executed with @, @@ or START.
set line 150
set arraysize 300 
spool   /home/oracle/data/out.csv
select /*+ parallel(8) */ 
x || ',' ||
x
FROM dual
where 1=0
;
spool off

DUMP with Parallelized Utl_File
Obviously, I/O is the bottleneck here. A dump program that writes to several files simultaneously  would greatly fasten the process. Here's an example which utilizes parallel pipelined function and utl_file.
However, in this case,  data is dumped on the server side, you need to download dump file from Oracle server, while spool output file stays on local machine.

CREATE SEQUENCE dump_seq;

CREATE TYPE dump_ot AS OBJECT ( 
      file_name  VARCHAR2(128), 
      no_records NUMBER      
);
  
CREATE TYPE dump_ott AS TABLE OF dump_ot;

CREATE OR REPLACE FUNCTION my_dump(p_input_cur SYS_REFCURSOR, p_file VARCHAR2)
  return dump_ott
  PIPELINED
  PARALLEL_ENABLE (PARTITION p_input_cur BY ANY)   
AS
  TYPE row_ntt IS TABLE OF VARCHAR2(32767);
  v_rows    row_ntt;

  v_file   utl_file.file_type;
  v_buffer VARCHAR2(32767);  
  v_num_lines   PLS_INTEGER := 0;
  v_name    VARCHAR2(128);
  c_eol     CONSTANT VARCHAR2(1) := CHR(10);
  c_eollen  CONSTANT PLS_INTEGER := LENGTH(c_eol);
  c_maxline CONSTANT PLS_INTEGER := 32767;
BEGIN
  v_name := p_file || dump_seq.nextval || '.dat';
  v_file := utl_file.fopen(LOCATION => 'EXT_DAT_DIR', filename => v_name, open_mode => 'w', max_linesize => 32767);
  
  LOOP
    FETCH p_input_cur BULK COLLECT INTO v_rows LIMIT 500;
    
    FOR i IN v_rows.FIRST .. v_rows.LAST LOOP
    
      IF LENGTH(v_rows(i)) + c_eollen + LENGTH(v_buffer) <= c_maxline THEN
        v_buffer := v_buffer || c_eol || v_rows(i);
      ELSE
        IF v_buffer IS NOT NULL THEN
          utl_file.put_line(v_file, v_buffer);
        END IF;
        v_buffer := v_rows(i);
      END IF;
      
    END LOOP;
    v_num_lines := v_num_lines + v_rows.COUNT;  
    EXIT WHEN p_input_cur%NOTFOUND;
  END LOOP;
  CLOSE p_input_cur;
  
  UTL_FILE.PUT_LINE(v_file, v_buffer);
  UTL_FILE.FCLOSE(v_file);
  
  PIPE ROW (dump_ot(v_name, v_num_lines));
  RETURN;
END;
经测试，该方法会造成各行之间串行。需要修正程序。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/638844/viewspace-776394/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/638844/viewspace-776394/