oracle导出文本文件的三种方法(spool,utl_file,sqluldr2)

一、常见的spool方法

二、utl_file包方法

三、sqluldr2工具

为了构建导出文本文件，先做点准备工作

1、扩充表空间

alter tablespace dams_data 
add datafile 'c:\oracle\oradata\orcl\damadata2.dbf' 
size 500m autoextend on maxsize 6000m; 

2、创建一张10万记录和50万记录的数据表

首先为了快速创建表数据用了connect by方法，再次为了把表存储搞大，每个字段长度都是1000字节，一条记录平均4000字节左右，数据库的db_block_size=8192字节，由于block还包括其他信息，所以一个块只能存储一条记录，10万记录大概在800m左右，50万记录为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), --大写字母随机
     dbms_random.string ('l', 5), --小写字母随机
     dbms_random.string ('a', 5), --混合字母随机
     dbms_random.string ('x', 5)  --字符串数字随机
   --dbms_random.string ('p', 5) --键盘字符随机
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), --大写字母随机
     dbms_random.string ('l', 5), --小写字母随机
     dbms_random.string ('a', 5), --混合字母随机
     dbms_random.string ('x', 5)  --字符串数字随机
   --dbms_random.string ('p', 5) --键盘字符随机
from (select level,rownum rn 
        from dual
     connect by rownum<=500000) a;
--164 seconds       
commit; 

3、简单做一下表分析

analyze table record10w compute statistics;  
analyze table record50w compute statistics;  

4、查看一下表的统计信息

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用来导出record10w记录

@c:\software\sqluldr2\spool10w.sql

spool c:\software\sqluldr2\data\record10wspool.txt 
set echo off  --不显示脚本中正在执行的sql语句
set feedback off --不显示sql查询或修改行数
set term off   --不在屏幕上显示
set heading off  --不显示列
set linesize 1000; //设置行宽，根据需要设置，默认100
select id||','||data1|| ',' ||data2 from record10w;  --需要导出的数据查询sql
spool off

定义spool50w.sql用来导出record50w记录

@c:\software\sqluldr2\spool50w.sql

spool c:\software\sqluldr2\data\record10wspool.txt 
set echo off  --不显示脚本中正在执行的sql语句
set feedback off --不显示sql查询或修改行数
set term off   --不在屏幕上显示
set heading off  --不显示列
set linesize 1000; //设置行宽，根据需要设置，默认100
select id||','||data1|| ',' ||data2 from record50w;  --需要导出的数据查询sql
spool off

在oracle command窗口中执行命令

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秒

补充

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包

这个包很久之前用过，好像效率也不错，在此不想尝试了，有兴趣的朋友可以试一下性能。

utl_file.fopen打开文件

utl_file.put_line写入记录

utl_file.fclose关闭文件

utl_file.fopen第一个参数为文件路径，不能直接指定绝对路径，需要建立directory，然后指定我们建立的directory

sqlplus / as sysdba
create directory my_dir as ‘/home/oracle/’;
grant read,write on directory dir_dump to hr;##也可以直接建立一个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

说实在的oracle对大批量大规模数据的导出做的很不友好，大概是基于某种自信吧，spool的效率一般很低，很多开源etl工具都是通过jdbc连接导出的，效率也好不到那里去

sqluldr2的作者是楼方鑫，oracle的大牛，原来淘宝的大神，有过几面之缘，是基于oci底层接口开发的文本导出工具。

sqluldr2小巧方便，使用方法类似于oracle自带的exp，支持自定义sql、本地和客户端的导出，速度快，效率高。

sqluldr2有几个版本，面向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语句
 field   = separator string between fields    #指定字段分隔符
 record  = separator string between records   #指定记录换行符
 rows    = print progress for every given rows (default, 1000000)     #输出导出记录日志
 file    = output file name(default: uldrdata.txt)    #导出数据文件名
 log     = log file name, prefix with + to append mode        #导出日志文件名
 fast    = auto tuning the session level parameters(yes)      #快速导出参数
 text    = output type (mysql, csv, mysqlins, oracleins, form, search).       #导出类型
 charset = character set name of the target database. #设置目标数据库字符集
 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

#设置查询条件为select * from record50w,导出文件头,导出文件名为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

具体执行见下面：

c:\software\sqluldr2>time
当前时间: 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
当前时间: 18:14:26.40 
--19秒

c:\software\sqluldr2>time
当前时间: 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
当前时间: 18:14:43.05
--7秒

总结：

总的来说，spool比较简单，但效率比较低

sqluldr2是基于oci接口开发的，性能上最快

utl_file，是oracle自带的包，可以测试一下