一、常見的spool方法

二、UTL_FILE包方法

三、sqluldr2工具

為了構(gòu)建導(dǎo)出文本文件，先做點(diǎn)準(zhǔn)備工作

1、擴(kuò)充表空間

ALTER TABLESPACE DAMS_DATA   ADD DATAFILE "C:\Oracle\oradata\orcl\DAMADATA2.DBF"  SIZE 500M AUTOEXTEND ON MAXSIZE 6000M; 

2、創(chuàng)建一張10萬記錄和50萬記錄的數(shù)據(jù)表

首先為了快速創(chuàng)建表數(shù)據(jù)用了CONNECT BY方法，再次為了把表存儲(chǔ)搞大，每個(gè)字段長(zhǎng)度都是1000字節(jié)，一條記錄平均4000字節(jié)左右，數(shù)據(jù)庫的db_block_size=8192字節(jié)，由于block還包括其他信息，所以一個(gè)塊只能存儲(chǔ)一條記錄，10萬記錄大概在800M左右，50萬記錄為4G

CREATE TABLE record10w(id      INT,data1   CHAR(1000),data2   CHAR(1000),data3   CHAR(1000),data4   CHAR(1000));INSERT INTO record10wSELECT a.rn,       DBMS_RANDOM.STRING ("u", 5), --大寫字母隨機(jī)       DBMS_RANDOM.STRING ("l", 5), --小寫字母隨機(jī)       DBMS_RANDOM.STRING ("a", 5), --混合字母隨機(jī)       DBMS_RANDOM.STRING ("x", 5)  --字符串?dāng)?shù)字隨機(jī)     --DBMS_RANDOM.STRING ("p", 5) --鍵盤字符隨機(jī)  FROM (SELECT level,ROWNUM rn   FROM DUAL       CONNECT BY ROWNUM<=100000) a;--27 seconds       COMMIT;     CREATE TABLE record50w(id      INT,data1   CHAR(1000),data2   CHAR(1000),data3   CHAR(1000),data4   CHAR(1000));INSERT INTO record50wSELECT a.rn,       DBMS_RANDOM.STRING ("u", 5), --大寫字母隨機(jī)       DBMS_RANDOM.STRING ("l", 5), --小寫字母隨機(jī)       DBMS_RANDOM.STRING ("a", 5), --混合字母隨機(jī)       DBMS_RANDOM.STRING ("x", 5)  --字符串?dāng)?shù)字隨機(jī)     --DBMS_RANDOM.STRING ("p", 5) --鍵盤字符隨機(jī)  FROM (SELECT level,ROWNUM rn   FROM DUAL       CONNECT BY ROWNUM<=500000) a;--164 seconds       COMMIT; 

3、簡(jiǎn)單做一下表分析

ANALYZE TABLE RECORD10W COMPUTE STATISTICS;  ANALYZE TABLE RECORD50W COMPUTE STATISTICS;  

4、查看一下表的統(tǒng)計(jì)信息

SELECT A.OWNER,A.TABLE_NAME,A.TABLESPACE_NAME,A.NUM_ROWS,A.BLOCKS,A.EMPTY_BLOCKS,A.AVG_ROW_LEN  FROM ALL_TABLES A  WHERE OWNER="METADATA"    AND TABLE_NAME IN ("RECORD10W","RECORD50W") 

方法一，spool方法

定義spool10w.sql用來導(dǎo)出record10w記錄

@C:\software\sqluldr2\spool10w.sql

SPOOL C:\software\sqluldr2\data\record10wspool.txt SET ECHO OFF  --不顯示腳本中正在執(zhí)行的SQL語句SET FEEDBACK OFF --不顯示sql查詢或修改行數(shù)SET TERM OFF   --不在屏幕上顯示SET HEADING OFF  --不顯示列SET LINESIZE 1000; //設(shè)置行寬，根據(jù)需要設(shè)置，默認(rèn)100select id||","||data1|| "," ||data2 FROM record10w;  --需要導(dǎo)出的數(shù)據(jù)查詢sqlSPOOL OFF

定義spool50w.sql用來導(dǎo)出record50w記錄

@C:\software\sqluldr2\spool50w.sql

SPOOL C:\software\sqluldr2\data\record10wspool.txt SET ECHO OFF  --不顯示腳本中正在執(zhí)行的SQL語句SET FEEDBACK OFF --不顯示sql查詢或修改行數(shù)SET TERM OFF   --不在屏幕上顯示SET HEADING OFF  --不顯示列SET LINESIZE 1000; //設(shè)置行寬，根據(jù)需要設(shè)置，默認(rèn)100select id||","||data1|| "," ||data2 FROM record50w;  --需要導(dǎo)出的數(shù)據(jù)查詢sqlSPOOL OFF

在Oracle Command窗口中執(zhí)行命令

SQL> set time on;18:09:32 SQL> @C:\software\sqluldr2\spool10w.sqlStarted spooling to C:\software\sqluldr2\data\record10wspool.txt--20秒18:09:51 SQL> @C:\software\sqluldr2\spool50w.sql18:10:52 SQL> --1分1秒

補(bǔ)充

sqlplus / as sysdbaset linesize 1000set pagesize 0set echo offset termout offset heading offset feedback offSET trims ONset term offSET trimspool ONSET trimout ONspool "/archlog/exp/test.txt";select OWNER||" , "||SEGMENT_NAME||" , "||PARTITION_NAME||" , " from dba_segments where rownum<10000;spool off;/

方法二、UTL_FILE包

這個(gè)包很久之前用過，好像效率也不錯(cuò)，在此不想嘗試了，有興趣的朋友可以試一下性能。

UTL_FILE.FOPEN打開文件

UTL_FILE.PUT_LINE寫入記錄

UTL_FILE.FCLOSE關(guān)閉文件

UTL_FILE.FOPEN第一個(gè)參數(shù)為文件路徑，不能直接指定絕對(duì)路徑，需要建立directory，然后指定我們建立的directory

sqlplus / as sysdba
create directory MY_DIR as ‘/home/oracle/’;
grant read,write on directory dir_dump to HR;##也可以直接建立一個(gè)public directory

CREATE OR REPLACE PROCEDURE test IStestjiao_handle UTL_FILE.file_type;BEGIN  test_handle := UTL_FILE.FOPEN("MY_DIR","test.txt","w");    FOR x IN (SELECT * FROM TESTJIAO) LOOP      UTL_FILE.PUT_LINE(test_handle,x.ID || "," || x.RQ ||",");    END LOOP;      UTL_FILE.FCLOSE(test_handle);EXCEPTION WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,2000));END;/

方法三、sqluldr2

說實(shí)在的Oracle對(duì)大批量大規(guī)模數(shù)據(jù)的導(dǎo)出做的很不友好，大概是基于某種自信吧，spool的效率一般很低，很多開源ETL工具都是通過JDBC連接導(dǎo)出的，效率也好不到那里去

sqluldr2的作者是樓方鑫，Oracle的大牛，原來淘寶的大神，有過幾面之緣，是基于OCI底層接口開發(fā)的文本導(dǎo)出工具。

sqluldr2小巧方便，使用方法類似于Oracle自帶的exp，支持自定義SQL、本地和客戶端的導(dǎo)出，速度快，效率高。

sqluldr2有幾個(gè)版本，面向linux和windows的，有32位和64位的,可自行找鏈接下載。

c:\software\sqluldr2>sqluldr264SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.License: Free for non-commercial useage, else 100 USD per server.Usage: SQLULDR2 keyword=value [,keyword=value,...]Valid Keywords:   user    = username/password@tnsname  #連接用戶/密碼@tns名稱   sql     = SQL file name      #指定SQL文件名   query   = select statement #指定SQL語句   field   = separator string between fields    #指定字段分隔符   record  = separator string between records   #指定記錄換行符   rows    = print progress for every given rows (default, 1000000)     #輸出導(dǎo)出記錄日志   file    = output file name(default: uldrdata.txt)    #導(dǎo)出數(shù)據(jù)文件名   log     = log file name, prefix with + to append mode#導(dǎo)出日志文件名   fast    = auto tuning the session level parameters(YES)      #快速導(dǎo)出參數(shù)   text    = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).       #導(dǎo)出類型   charset = character set name of the target database. #設(shè)置目標(biāo)數(shù)據(jù)庫字符集   ncharset= national character set name of the target database.   parfile = read command option from parameter file      for field and record, you can use "0x" to specify hex character code,  \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 "=0x27

#設(shè)置查詢條件為select * from record50w,導(dǎo)出文件頭,導(dǎo)出文件名為record50wsqluldr2.csv,日志文件名為record50wsqluldr2.log，控制文件名為record50w_sqlldr.ctl

sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record50w" head=yes file=C:\software\sqluldr2\data\record50wsqluldr2.csv log=C:\software\sqluldr2\log\record50wsqluldr2.log table=record50w

sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record10w" head=yes file=C:\software\sqluldr2\data\record10wsqluldr2.csv log=C:\software\sqluldr2\log\record10wsqluldr2.log table=record10w

具體執(zhí)行見下面：

c:\software\sqluldr2>time當(dāng)前時(shí)間: 18:14:07.92c:\software\sqluldr2>sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record50w" head=yes file=C:\software\sqluldr2\data\record50wsqluldr2.csv log=C:\software\sqluldr2\log\record50wsqluldr2.log table=record50wc:\software\sqluldr2>time當(dāng)前時(shí)間: 18:14:26.40 --19秒c:\software\sqluldr2>time當(dāng)前時(shí)間: 18:14:36.83c:\software\sqluldr2>sqluldr264 metadata/XXXXXX@127.0.0.1:1521/orcl query="select id,data1,data2 from record10w" head=yes file=C:\software\sqluldr2\data\record10wsqluldr2.csv log=C:\software\sqluldr2\log\record10wsqluldr2.log table=record10wc:\software\sqluldr2>time當(dāng)前時(shí)間: 18:14:43.05--7秒

總結(jié)：

總的來說，Spool比較簡(jiǎn)單，但效率比較低

sqluldr2是基于OCI接口開發(fā)的，性能上最快

UTL_FILE，是Oracle自帶的包，可以測(cè)試一下