背景
最近需要协助运营部分定期出csv格式数据，每次用客户端导出很麻烦，于是研究了下sqluldr2工具，写了个定时导出的脚本。
下载:
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
查看参数
[oracle@localhost sqluldr2]$ ./sqluldr2 -help
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
sql = SQL file name
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.
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
参数介绍示例
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
