Oracle使用sqluldr2匯出資料

甲骨文技術支援發表於2018-01-16
OracleSQL


sqluldr2是一個基於OCI的文字匯出工具，小巧方便，使用方法類似於Oracle自帶的exp，支援自定義SQL、本地和客戶端的匯出，速度快，效率高。

下載地址，最下面有個小工具裡面就有

  1. http://www.onexsoft.com/download.html
也可以直接在系統上wget

  1. wget http://www.onexsoft.com/software/sqluldr2linux64.zip
解壓之後就是一個bin檔案

  1. -rwxrwxrwx 1 oracle oinstall 185894 1月 27 2016 sqluldr2linux64.bin
直接執行就能顯示幫助

  1. [oracle@hecomoracleserver2 sqluldr2]$ ./sqluldr2linux64.bin

  3. SQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
  4. (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.

  6. License: Free for non-commercial useage, else 100 USD per server.

  8. Usage: SQLULDR2 keyword=value [,keyword=value,...]

  10. Valid Keywords:
  11.    user = username/password@tnsname
  12.    sql = SQL file name
  13.    query = select statement
  14.    field = separator string between fields
  15.    record = separator string between records
  16.    rows = print progress for every given rows (default, 1000000)
  17.    file = output file name(default: uldrdata.txt)
  18.    log = log file name, prefix with + to append mode
  19.    fast = auto tuning the session level parameters(YES)
  20.    text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
  21.    charset = character set name of the target database.
  22.    ncharset= national character set name of the target database.
  23.    parfile = read command option from parameter file

  25.   for field and record, you can use '0x' to specify hex character code,
  26.   \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22
上面的引數中，比較常用的是：
    
    query：用這個引數可以自定義匯出語句
    file：匯出的檔名字和目錄
    log：日誌
    parfile：可以把引數放到parfile檔案裡，這個引數對於複雜sql很有用。
    field:設定匯出檔案裡的分隔符

連線資料的方法：

        本地連線方式：

  1. ./sqluldr2linux64.bin user=gltest/gltest query="select * from T_H_BK_CUSTOMER_20180114_0;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv
       客戶端連線：TNS方式

  1. ./sqluldr2linux64.bin user=gltest/gltest@orcl query="select * from T_H_BK_CUSTOMER_20180114_0;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv
      客戶端連線：直接連線方式

  1. ./sqluldr2linux64.bin user=gltest/gltest@192.168.128.98:1521/orcl query="select * from T_H_BK_CUSTOMER_20180114_0;" file=/home/oracle/sqluldr2/T_H_BK_CUSTOMER_20180114_0.csv

測試匯出速度：

        因為我的sql比較長，所以用了parfile:

  1. [oracle@gloracleserver2 sqluldr2]$ more customer.txt
  2. user=gltest/gltest
  3. query=select ent_code,code,name,cust_levels,dept_code,poi_id,status,createon,createby,lastupdateon from v30_md_customer;
       看看匯出輸出，1100萬資料不到30秒，速度很快。

  1. [oracle@gloracleserver2 sqluldr2]$ ./sqluldr2linux64.bin parfile=/home/oracle/sqluldr2/customer.txt file=/home/oracle/sqluldr2/customer.csv
  2.            0 rows exported at 2018-01-16 20:25:09, size 0 MB.
  3.      1000000 rows exported at 2018-01-16 20:25:11, size 96 MB.
  4.      2000000 rows exported at 2018-01-16 20:25:14, size 196 MB.
  5.      3000000 rows exported at 2018-01-16 20:25:16, size 296 MB.
  6.      4000000 rows exported at 2018-01-16 20:25:18, size 392 MB.
  7.      5000000 rows exported at 2018-01-16 20:25:21, size 492 MB.
  8.      6000000 rows exported at 2018-01-16 20:25:23, size 592 MB.
  9.      7000000 rows exported at 2018-01-16 20:25:26, size 692 MB.
  10.      8000000 rows exported at 2018-01-16 20:25:28, size 788 MB.
  11.      9000000 rows exported at 2018-01-16 20:25:31, size 888 MB.
  12.     10000000 rows exported at 2018-01-16 20:25:34, size 988 MB.
  13.     11000000 rows exported at 2018-01-16 20:25:36, size 1088 MB.
  14.     11317061 rows exported at 2018-01-16 20:25:37, size 1121 MB.
  15.          output file /home/oracle/sqluldr2/customer.csv closed at 11317061 rows, size 1121 MB.

遇到的錯誤：

        1. 匯出檔案裡有中文顯示亂碼，需要設定引數charset=UTF8

        2. ORA-24345: A Truncation or null fetch error occurred，設定引數safe=yes


 





來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20893244/viewspace-2150202/，如需轉載，請註明出處，否則將追究法律責任。

相關文章