SQL*Loader是Oracle提供的一个非常有用的工具,可以实现批量数据的高速加载,它将数据从外部文件加载到Oracle数据库的表中,本篇将对Oracle 12c的SQL*Loader进行简单的介绍和演示。
1 SQL*Loader介绍
SQL*Loader的使用,是通过Oracle的命令行指令sqlldr实现,该指令是客户端工具,可通过KEY-VALUE的形式指定对应的选项,或者使用参数文件和控制文件来实现,下面展示的是SQL*Loader的工作原理:
控制文件主要用于控制数据加载的行为,包括在哪里找到数据,如何解析和解释数据,以将数据插入到哪里等等,通常来说,控制文件包括三部分内容:
- 会话范围的信息;
- 表和字段列表信息;
- 输入数据;
对于数据的加载,SQL*Loader提供了三种方法分别是:
- 传统路径加载;
- 直接路径加载;
- 外部表加载;
2 SQL*Loader指令语法
SQL*Loader通过指令sqlldr实现,输入该命令回车,即可看到该指令对应的选项信息:
[oracle@odd ~]$ sqlldr
SQL*Loader: Release 12.1.0.2.0 - Production on Fri May 8 20:08:37 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conver