美文网首页我爱编程
【Oracle】Sqluldr大批量数据导出

【Oracle】Sqluldr大批量数据导出

作者: 半个橙子 | 来源:发表于2018-06-19 16:12 被阅读0次

    Sqluldr2

    使用pl/sql导出Excel最多只能导出65535条数据,而且数据导出比较慢;查看全部数据还会出现异常

    数据量过大

    sqluldr2是oracle数据导出工具,它可以将数据以csv、txt等格式导出,适用于大批量数据的导出,导出速度非常快。导出后可以使用oracle loader工具将数据导入。

    sqluldr2下载

    https://pan.baidu.com/s/1A9OUi4WlNT61XZzw-dpGZw
    https://blog.csdn.net/howie_zhw/article/details/53491692

    命令参数

    C:\Users\pengyunlong>C:\Users\pengyunlong\Downloads\sqluldr2\oracle数据快速导出工具\sqluldr2.exe
    
    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 #用户名/密码@数据库IP/实例名
       sql     = SQL file name  #查询数据的sql文件路径
       query   = select statement #查询语句
       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. #目标数据库的编码UTF8
       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
    

    示例

    1. 根据查询语句导出
    sqluldr2_linux64_10204.bin user="username/pwd" 
    query="select * from table" field="0x09" 
    record=0x0a file="/opt/data/table.txt" charset=UTF8  
    
    1. 查询语句过长可以将语句写入sql文件中
    • sql=使用queryMerPSql.sql里面的sql语句来执行查询操作
    • field=结果字段使用逗号分割,
    • record=0x0a 行间使用\n分割
    • file= 导出文件名为6.csv
    • head=yes 带表头
    • rows=10000 每隔10000行输出日志
    • log=+ 输出日志位置
    C:\Users\pengyunlong>sqluldr2.exe user="username/password@10.51.**.**/hdb" 
    sql="C:\Users\pengyunlong\Desktop\导出数据\queryMerPSql.sql" field="0x2c" record=0x0a 
    file="C:\Users\pengyunlong\Desktop\导出数据\queryMerPSql\6.csv"  head=yes  rows=10000 
    log=+C:\Users\pengyunlong\Desktop\导出数据\tmp001.log
    
    • 查看日志导出日志
               0 rows exported at 2018-06-19 16:37:49, size 0 MB.
           10000 rows exported at 2018-06-19 16:37:49, size 0 MB.
           20000 rows exported at 2018-06-19 16:37:49, size 0 MB.
           30000 rows exported at 2018-06-19 16:37:50, size 0 MB.
           40000 rows exported at 2018-06-19 16:37:50, size 0 MB.
           50000 rows exported at 2018-06-19 16:37:50, size 0 MB.
           60000 rows exported at 2018-06-19 16:37:50, size 0 MB.
           70000 rows exported at 2018-06-19 16:37:51, size 0 MB.
           80000 rows exported at 2018-06-19 16:37:51, size 0 MB.
           90000 rows exported at 2018-06-19 16:37:51, size 0 MB.
          100000 rows exported at 2018-06-19 16:37:51, size 0 MB.
          110000 rows exported at 2018-06-19 16:37:51, size 0 MB.
          120000 rows exported at 2018-06-19 16:37:52, size 4 MB.
          130000 rows exported at 2018-06-19 16:37:52, size 4 MB.
          140000 rows exported at 2018-06-19 16:37:52, size 4 MB.
          150000 rows exported at 2018-06-19 16:37:52, size 4 MB.
          160000 rows exported at 2018-06-19 16:37:53, size 4 MB.
          170000 rows exported at 2018-06-19 16:37:53, size 4 MB.
          180000 rows exported at 2018-06-19 16:37:53, size 4 MB.
          190000 rows exported at 2018-06-19 16:37:53, size 4 MB.
          200000 rows exported at 2018-06-19 16:37:53, size 4 MB.
          210000 rows exported at 2018-06-19 16:37:54, size 4 MB.
          220000 rows exported at 2018-06-19 16:37:54, size 4 MB.
          226482 rows exported at 2018-06-19 16:37:54, size 8 MB.
    output file C:\Users\pengyunlong\Desktop\导出数据\queryMerPSql\6.csv 
    closed at 42295 rows, size 5 MB.
    

    相关文章

      网友评论

        本文标题:【Oracle】Sqluldr大批量数据导出

        本文链接:https://www.haomeiwen.com/subject/zppaeftx.html