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

ALTER TABLESPACE DAMS_DATA

   ADD DATAFILE 'C:\Oracle\oradata\orcl\DAMADATA2.DBF'

  SIZE 500M AUTOEXTEND ON MAXSIZE 6000M;

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 ;

ANALYZE TABLE RECORD10W COMPUTE STATISTICS ; 

ANALYZE TABLE RECORD50W COMPUTE STATISTICS ;

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

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

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 ;

/

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 ;

/

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

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秒