oracle導(dǎo)出文本文件的三種方法(spool,utl_file,sqluldr2)

一、常見(jiàn)的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萬(wàn)記錄和50萬(wàn)記錄的數(shù)據(jù)表

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

create table record10w
(
      id      int,
      data1   char(1000),
      data2   char(1000),
      data3   char(1000),
      data4   char(1000)
);
insert into record10w
select a.rn,
     dbms_random.string ('u', 5), --大寫(xiě)字母隨機(jī)
     dbms_random.string ('l', 5), --小寫(xiě)字母隨機(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 record50w
select a.rn,
     dbms_random.string ('u', 5), --大寫(xiě)字母隨機(jī)
     dbms_random.string ('l', 5), --小寫(xiě)字母隨機(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用來(lái)導(dǎo)出record10w記錄

@c:\software\sqluldr2\spool10w.sql

spool c:\software\sqluldr2\data\record10wspool.txt 
set echo off  --不顯示腳本中正在執(zhí)行的sql語(yǔ)句
set feedback off --不顯示sql查詢或修改行數(shù)
set term off   --不在屏幕上顯示
set heading off  --不顯示列
set linesize 1000; //設(shè)置行寬，根據(jù)需要設(shè)置，默認(rèn)100
select id||','||data1|| ',' ||data2 from record10w;  --需要導(dǎo)出的數(shù)據(jù)查詢sql
spool off

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

@c:\software\sqluldr2\spool50w.sql

spool c:\software\sqluldr2\data\record10wspool.txt 
set echo off  --不顯示腳本中正在執(zhí)行的sql語(yǔ)句
set feedback off --不顯示sql查詢或修改行數(shù)
set term off   --不在屏幕上顯示
set heading off  --不顯示列
set linesize 1000; //設(shè)置行寬，根據(jù)需要設(shè)置，默認(rèn)100
select id||','||data1|| ',' ||data2 from record50w;  --需要導(dǎo)出的數(shù)據(jù)查詢sql
spool off

在oracle command窗口中執(zhí)行命令

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

補(bǔ)充

sqlplus / as sysdba

set linesize 1000
set pagesize 0
set echo off
set termout off
set heading off
set feedback off
set trims on
set term off
set trimspool on
set trimout on
spool '/archlog/exp/test.txt';
select owner||' , '||segment_name||' , '||partition_name||' , ' from dba_segments where rownum<10000;
spool off;
/

方法二、utl_file包

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

utl_file.fopen打開(kāi)文件

utl_file.put_line寫(xiě)入記錄

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
is
testjiao_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

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

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

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

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

c:\software\sqluldr2>sqluldr264
sql*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語(yǔ)句
 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ù)庫(kù)字符集
 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í)行見(jiàn)下面：

c:\software\sqluldr2>time
當(dāng)前時(shí)間: 18:14:07.92
c:\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=record50w
c:\software\sqluldr2>time
當(dāng)前時(shí)間: 18:14:26.40 
--19秒

c:\software\sqluldr2>time
當(dāng)前時(shí)間: 18:14:36.83
c:\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=record10w
c:\software\sqluldr2>time
當(dāng)前時(shí)間: 18:14:43.05
--7秒

總結(jié)：

總的來(lái)說(shuō)，spool比較簡(jiǎn)單，但效率比較低

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

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