一、常见的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')
定义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.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; /
说实在的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自带的包,可以测试一下
相关文章: