一、背景
在别的机器配置了Oracle数据库,本机配置了Oracle客户端。今天刚好要录一份1000w的数据到Oracle数据库测试程序,Google一番之后,初定两种录入方式:
- Navicat直接导入。有图形界面,傻瓜式操作,但速度稍微慢了些,1000w的数据录了2小时。
- 通过sqlldr工具批量录入。1000w大概15分钟录完,不过要写个ctl文件。
第一种方案太慢了,不能满足需求,决定采用第二个想法。
二、配置sqlldr
顺便提下怎么配置Oracle客户端,先到Oracle客户端下载图中标识的几个文件,sqlldr工具在instantclient-tools-windows.x64-12.2.0.1.0.zip
中。
将这几个文件解压成一个文件夹,可以看到instantclient_12_2
文件夹中有sqlldr.exe
。
在instantclient_12_2
中新建文件夹network\admin
,然后在里面新建tnsnames.ora
文件,结合自身情况写入以下内容,再配置好环境变量。
服务器连接别名 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = 通讯协议)(HOST = 远程服务器IP地址)(PORT = 监听端口))
)
(CONNECT_DATA =
(SERVICE_NAME = 数据库服务名称)
)
)
例如:
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.20.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
配置环境变量:
TNS_ADMIN
D:\software\dev\sql\oracle\instantclient_12_2\network\admin
ORACLE_HOME
D:\software\dev\sql\oracle\instantclient_12_2
NLS_LANG
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
在PATH中加入ORACLE_HOME,即:D:\software\dev\sql\oracle\instantclient_12_2
重启电脑,等待下一步操作。
打开终端,运行sqlldr
命令,运行正常表示配置成功。
三、编写ctl
参考sqlldr的用法 (这个最完整),了解大概语法。
示例:将test.csv的内容录入到temp_sqlldr_test表中
OPTIONS (SKIP=1,ROWS=1024)
LOAD DATA
INFILE 'test.csv'
APPEND
INTO TABLE temp_sqlldr_test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
field_1,
field_2,
field_3
)
需要注意的一些点:
# 录入方式
INSERT,默认模式,要求目标表为空
APPEND,追加内容
TRUNCATE,清空目标表再录入
# 可选项
TRAILING NULLCOLS,表字段没有对应的值时允许为空
# 文件默认分隔符(根据实际情况修改)
txt文件是制表符,即'\t'
csv文件是逗号,即','
四、录入数据
Oracle数据库装在远程服务器,我本机只是使用Oracle客户端进行连接,所以需要给出远程服务器信息,如下:
格式:
sqlldr 数据库名称/数据库密码@远程服务器IP地址:端口/实例名 control=ctl文件名
运行命令:sqlldr test/test123@192.168.20.1:1521/testdb control=start.ctl
如果在使用sqlldr插入数据时发生错误,可以查看对应的日志文件追踪问题,通常是字段类型、字段大小不匹配、文件分隔符的问题。