Dump with SPOOL
set colsep , --set column separator to comma
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) */ dummy
FROM dual
where 1=0
;
spool off
Best practices:
1. 尽量在数据库服务器上执行spool,以避免客户端-服务器之间的网络延迟。数据量大时,网络延迟是个重要因素。
2. 使用Partition pruning导出大表的数据。
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.
经测试,该方法会造成各行之间串行。需要修正程序。
1. 尽量在数据库服务器上执行spool,以避免客户端-服务器之间的网络延迟。数据量大时,网络延迟是个重要因素。
2. 使用Partition pruning导出大表的数据。
DUMP with Parallelized Utl_File
Obviously, I/O is the bottleneck here. A dump program that writes to several files simultaneouslywould 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;
经测试,该方法会造成各行之间串行。需要修正程序。