Oracle 导出CSV工具-sqluldr2

背景

  做DBA这么多年，最多的需求就是数据导入导出，为公司导各种经营数据。一般数据量小基本用软就可以搞定了，但有一些变态的需求，要1年内的交易流水之类的数据，软件导直接就崩溃不动了。最近也是需要协助运营部分定期出csv格式数据，每次用客户端导出很麻烦，于是《sqluldr2工具》就让我用起来吧。

• 导入工具：《SQLLDR 导入碎片化CSV数据脚本》上个月刚整理了一份，有需要的朋友可以参阅，欢迎指正及提意见。

下载:

sqluldr2_linux64_10204.zip

目录规划

• 通过脚本自动化导出，需要规划好目录：

[oracle@localhost~]$ tree csv_dat/
csv_dat/
|-- csv_data       # 存放CSV文件
|   `-- t2.csv
|-- csv_log        # 存放日志
|   `-- t2.log
|-- csv_sql        # 存放SQL文本
|   `-- t2.sql
`-- sqluldr2       # 存放：sqluldr2_linux64_10204.bin
    `-- sqluldr2   # mv sqluldr2_linux64_10204.bin sqluldr2  & chmod 775 sqluldr2/sqluldr2 

查看参数

• sqluldr2 -help

   user    = 用户名/密码@数据库地址
   sql     = SQL 文件名
   query   = 查询语句
   field   = 字段之间的分隔符字符串
   record  = 记录之间的分隔符字符串
   rows    = 打印每个给定行的进度（默认值，1000000）
   file    = 输出文件名(default: uldrdata.txt)
   log     = 日志文件名，前缀+表示追加模式
   fast    = 自动调整会话级参数（YES）
   text    = 输出类型（MYSQL、CSV、MYSQLINS、ORACLEINS、FORM）
   charset = 目标数据库的字符集名称
   ncharset= 目标数据库的国家字符集名称
   parfile = 从参数文件中读取命令选项
在指定分隔符时，可以用字符的ASCII代码(0xXX，大写的XX为16进制的ASCII码值)来指定一个字符，常用的字符的ASCII代码如下：
回车=0x0d，换行=0x0a，TAB键=0x09，|=0x7c，&=0x26，井号=0x23，双引号=0x22，单引号=0x27，冒号=0x3a

参数介绍示例

field-指定分隔符

• 默认是逗号分隔符

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log
[oracle@localhost csv_data]$ cat t2.csv 
53,B,100,status:B,2024-03-26 13:37:28.000000
54,B,123,status:B,2023-01-26 13:37:28.000000

• 指定分隔符‘;’

[oracle@localhost sqluldr2]$  ./sqluldr2 user=scott/tiger query="select * from t2" field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log
[oracle@localhost csv_data]$ cat t2.csv 
53;B;100;status:B;2024-03-26 13:37:28.000000
54;B;123;status:B;2023-01-26 13:37:28.000000

query-SQL调用

• 直接写表名

[oracle@localhost sqluldr2]$  ./sqluldr2 user=scott/tiger query="t2" file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log
- 默认生成ctl 控制文件
[oracle@localhost sqluldr2]$ ls
sqluldr2  t2_sqlldr.ctl
[oracle@localhost sqluldr2]$ cat t2_sqlldr.ctl 
--
-- SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1
-- (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
--
--  CREATE TABLE t2 (
--    ID NUMBER(16),
--    STATUS VARCHAR2(2),
--    AMT NUMBER(5,2),
--    COMMENTS VARCHAR2(1000),
--    CREATE_TIME TIMESTAMP
--  );
--
OPTIONS(BINDSIZE=2097152,READSIZE=2097152,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE '/home/oracle/csv_dat/csv_data/t2.csv' "STR X'0a'"
INSERT INTO TABLE t2
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS 
(
  "ID" CHAR(18) NULLIF "ID"=BLANKS,
  "STATUS" CHAR(2) NULLIF "STATUS"=BLANKS,
  "AMT" CHAR(8) NULLIF "AMT"=BLANKS,
  "COMMENTS" CHAR(1000) NULLIF "COMMENTS"=BLANKS,
  "CREATE_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "CREATE_TIME"=BLANKS
)

[oracle@localhost csv_data]$ cat t2.csv 
53,B,100,status:B,2024-03-26 13:37:28.000000
54,B,123,status:B,2023-01-26 13:37:28.000000

• 指定控制文件：control

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="t2"  control=/home/oracle/csv_dat/csv_data/t2.ctl file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost sqluldr2]$ ls
sqluldr2

[oracle@localhost csv_data]$ ls
t2.csv  t2.ctl

• 直接写SQL文件见field 示例，在此不演示

head=yes- 输出表头

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" head=yes field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_data]$ cat t2.csv 
ID;STATUS;AMT;COMMENTS;CREATE_TIME
53;B;100;status:B;2024-03-26 13:37:28.000000
54;B;123;status:B;2023-01-26 13:37:28.000000

SQL - 指定SQL 文件本

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger sql=/home/oracle/csv_dat/csv_sql/t2.sql field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_sql]$ cat t2.sql 
select * from t2

[oracle@localhost csv_data]$ cat t2.csv 
53;B;100;status:B;2024-03-26 13:37:28.000000
54;B;123;status:B;2023-01-26 13:37:28.000000

log - 日志输出

• 指定日志输出，在此不再演示；
• log=+1.log 日志追加

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2" head=yes field=';' file=/home/oracle/csv_dat/csv_data/t2.csv log=+/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_log]$ cat t2.log 
           0 rows exported at 2024-07-23 15:38:46, size 0 MB.
           2 rows exported at 2024-07-23 15:38:46, size 0 MB.
         output file /home/oracle/csv_dat/csv_data/t2.csv closed at 2 rows, size 0 MB.
           0 rows exported at 2024-07-23 15:41:16, size 0 MB.
           2 rows exported at 2024-07-23 15:41:16, size 0 MB.
         output file /home/oracle/csv_dat/csv_data/t2.csv closed at 2 rows, size 0 MB.

数据切片: rows 与 batch=yes 使用才会有效果

• rows 单独使用

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2 where rownum<10000" rows=1000   file=/home/oracle/csv_dat/csv_data/t2_%B.csv log=+/home/oracle/csv_dat/csv_log/t2.log   

[oracle@localhost csv_data]$ ls
t2_1.csv

[oracle@localhost csv_data]$ wc -l t2_1.csv 
9999 t2_1.csv

• rows 与 batch=yes - 按记录切片

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2  where rownum<10000" rows=1000  batch=yes  file=/home/oracle/csv_dat/csv_data/t2_%B.csv log=+/home/oracle/csv_dat/csv_log/t2.log  

[oracle@localhost csv_data]$ ls
t2_1.csv  t2_10.csv  t2_2.csv  t2_3.csv  t2_4.csv  t2_5.csv  t2_6.csv  t2_7.csv  t2_8.csv  t2_9.csv
[oracle@localhost csv_data]$ wc -l t2_1.csv 
1000 t2_1.csv
[oracle@localhost csv_data]$ wc -l t2_2.csv 
1000 t2_2.csv

• size 与 batch=yes - 按大小切片

[oracle@localhost sqluldr2]$ ./sqluldr2 user=scott/tiger query="select * from t2 where rownum<100000" size=10MB   batch=yes  file=/home/oracle/csv_dat/csv_data/t2_%B.csv log=+/home/oracle/csv_dat/csv_log/t2.log

[oracle@localhost csv_data]$ du -sh *
12M     t2_1.csv
12M     t2_2.csv
8.0M    t2_3.csv
12M     t2_4.csv
7.8M    t2_5.csv

总结

  以上参数为常用参数，结合shell脚本可以使用导出工作自动化、更加轻便化，如果再部署一套邮件服务器，使邮件自动化发送，实现喝着咖啡听着歌完成工作。
  经验全是用出来的，善于总结让经验更加精炼。在现在这个逆风的环境中，深挖自己的地基，夯实基本才能让自己走的更远…