Oracle導出文本文件的方法有哪些

這篇文章主要介紹“Oracle導出文本文件的方法有哪些”，在日常操作中，相信很多人在Oracle導出文本文件的方法有哪些問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Oracle導出文本文件的方法有哪些”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

為了構建導出文本文件，先做點準備工作

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秒

到此，關于“Oracle導出文本文件的方法有哪些”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！