转载自:
http://jackass.io/2009/03/18/oracle-fast-parallel-data-unload-into-ascii-files/

CREATE OR REPLACE DIRECTORY "MYDIR" AS '/home/oracle/';
CREATE OR REPLACE TYPE dump_ot AS OBJECT
(
file_name VARCHAR2 (128),
directory_name VARCHAR2 (128),
no_records NUMBER,
session_id NUMBER,
start_dttm TIMESTAMP WITH TIME ZONE,
end_dttm TIMESTAMP WITH TIME ZONE
);
CREATE OR REPLACE TYPE dump_ntt AS TABLE OF dump_ot;
CREATE OR REPLACE FUNCTION DATA_UNLOAD (
p_source IN SYS_REFCURSOR,
p_filename IN VARCHAR2,
p_directory IN VARCHAR2,
p_unique_filename IN VARCHAR2 DEFAULT 'N',
p_create_log_file IN VARCHAR2 DEFAULT 'N')
RETURN dump_ntt
PIPELINED
PARALLEL_ENABLE(PARTITION p_source BY ANY)
AS
/*
p_source SQL query you spool
p_filename targe file name
p_directory targe Oracle directory
p_unique_filename create unique file name? Y/N (appends unique SID to p_filename) - useful only for parallel unload
p_create_log_file create log file? Y/N (creates separate log file and logs every 1mm rows) - has very small performance hit on the spool
*/
TYPE row_ntt IS TABLE OF VARCHAR2 (32767);
v_rows row_ntt;
v_file UTL_FILE.FILE_TYPE;
v_log_file UTL_FILE.FILE_TYPE;
v_buffer VARCHAR2 (32767);
v_sid VARCHAR (255);
v_name VARCHAR2 (255);
v_lines PLS_INTEGER := 0;
v_start_dttm TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
v_end_dttm TIMESTAMP WITH TIME ZONE;
v_create_log BOOLEAN := FALSE;
c_eol CONSTANT VARCHAR2 (1) := CHR (10);
c_eollen CONSTANT PLS_INTEGER := LENGTH (c_eol);
c_maxline CONSTANT PLS_INTEGER := 32767;
c_log_limit CONSTANT PLS_INTEGER := 1000000;
BEGIN
v_sid := LPAD (SYS_CONTEXT ('USERENV', 'sid'), 10, '0');
v_name := p_filename;
IF TRIM (UPPER (p_create_log_file)) = 'Y'
THEN
v_create_log := TRUE;
END IF;
-- add SID (must be used for parallel spooling, single spooling or spooling across db-link creates alsway one file)
IF UPPER (p_unique_filename) = 'Y'
THEN
v_name := v_name || '_' || TO_CHAR (v_sid);
END IF;
v_file :=
UTL_FILE.FOPEN (p_directory,
v_name,
'w',
c_maxline);
IF v_create_log
THEN
v_log_file :=
UTL_FILE.FOPEN (p_directory,
v_name || '.LOG',
'w',
c_maxline);
UTL_FILE.PUT_LINE (
v_log_file,
TO_CHAR (v_start_dttm, 'DD-MON-YYYY HH24:MI:SS:FF3') || ' --> START');
UTL_FILE.FFLUSH (v_log_file);
END IF;
LOOP
FETCH p_source
BULK COLLECT INTO v_rows
LIMIT 10000;
FOR i IN 1 .. v_rows.COUNT
LOOP
IF LENGTHB (v_buffer) + c_eollen + LENGTHB (v_rows (i)) <= 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_lines := v_lines + v_rows.COUNT;
IF v_create_log AND MOD (v_lines, c_log_limit) = 0
THEN
UTL_FILE.PUT_LINE (
v_log_file,
TO_CHAR (SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS:FF3')
|| ' --> '
|| v_lines);
UTL_FILE.FFLUSH (v_log_file);
END IF;
EXIT WHEN p_source%NOTFOUND;
END LOOP;
CLOSE p_source;
UTL_FILE.PUT_LINE (v_file, v_buffer);
UTL_FILE.FCLOSE (v_file);
v_end_dttm := SYSTIMESTAMP;
IF v_create_log
THEN
UTL_FILE.PUT_LINE (
v_log_file,
TO_CHAR (v_end_dttm, 'DD-MON-YYYY HH24:MI:SS:FF3')
|| ' --> '
|| v_lines);
UTL_FILE.PUT_LINE (
v_log_file,
TO_CHAR (v_end_dttm, 'DD-MON-YYYY HH24:MI:SS:FF3') || ' --> END');
UTL_FILE.FCLOSE (v_log_file);
END IF;
PIPE ROW (dump_ot (v_name,
p_directory,
v_lines,
v_sid,
v_start_dttm,
v_end_dttm));
RETURN;
END;
/
SELECT *
FROM TABLE (
DATA_UNLOAD (
CURSOR (
SELECT /*+ parallel(u,4) */
log_id
|| ','
|| typeid
|| ','
|| to_id
|| ','
|| to_msg
|| ','
|| userid
|| ','
|| nickname
|| ','
|| showing
|| ','
|| fromip
|| ','
|| TO_CHAR (createtime, 'yyyy-mm-dd HH24:mi:ss')
FROM mvbox_space.user_visit u),
'SAMPLE_SPOOL.TXT',
'MYDIR',
'Y',
'N'));

ITPub博客

Oracle快速导出平面文件

最新文章