Oracle导出文本文件的三种方法(spool,UTL_FILE,sqluldr2)_Oracle

一、常见的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')

Oracle导出文本文件的三种方法(spool,UTL_FILE,sqluldr2)

方法一，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秒