Oracle SQL*Loader 的使用简介

前面一文简单介绍了 sqluldr2 的安装与使用，sqluldr2 的诞生主要是用于将大批量的 Oracle 数据快速导出成 CSV/Text 文本格式，方便导入到其他数据库中，如今国产化进行的如火如荼，这个工具也是在国产数据库迁移中使用比较广泛的工具，值得大家去学习与使用，今天要说的是 Oracle 数据库自带的数据导入工具 SQL*Loader(sqlldr)，只要你安装了 Oracle 数据库，那么这个工具就存在于 ORACLE_HOME/bin 目录下，它的功能是将从其他数据库中导出的 DAT/CSV/Text 文件加载到 Oracle 数据库中。

SQL*Loader 原理

将外部数据（比如文本型）导入Oracle 数据库中。（可用于不同类型数据库数据迁移）本质是在段（segment 表）重新 insert 记录。

①conventional：将记录插入到segment的HWM(高水位线）以下的块，要首先访问bitmap ，来确定那些block 有free space

②direct path：将记录插入到segment的HWM(高水位线）以上的从未使用过的块，绕过db_buffer, 不检查约束。还可以关闭redo, 也支持并行操作，加快插入速度。

传统插入数据和直接插入数据：

SQL> create table emp1 as select * from emp where 1=2;

SQL> insert into emp1 select * from emp; 传统方式数据

SQL> insert /*+ APPEND */ into emp1 select * from emp; 直接方式数据，必须commit后才能查看数据

【实验】直接路径插入数据

SQL> create table emp1 as select * from emp where 1=2;

SQL> insert into emp1 select * from emp; conventional 传统方式数据

SQL> insert /*+ APPEND */ into emp1 select * from emp;  直接方式数据，必须 commit 后才能查看数据

创建表插入数据

SCOTT@PROD>create table test as select * from emp;

SCOTT@PROD>insert into test select * from test;

SCOTT@PROD>/

SCOTT@PROD>delete from test;

SCOTT@PROD>commit;

SCOTT@PROD>analyze table test compute statistics;

SCOTT@PROD>select table_name,blocks,empty_blocks from user_tables where table_name='TEST';

普通 insert

SCOTT@PROD>insert into test select * from emp;

SCOTT@PROD>commit;

SCOTT@PROD>analyze table test compute statistics;

SCOTT@PROD>select table_name,blocks,empty_blocks from user_tables where table_name='TEST';

直接路径插入

SCOTT@PROD>insert /*+append*/ into test select * from emp;

SCOTT@PROD>analyze table test compute statistics;

SCOTT@PROD>select table_name,blocks,empty_blocks from user_tables where table_name='TEST';

Sqlldr 用法

sqlldr test/mypassword@pdb1 /home/oracle/test.ctl log=test.log

[oracle@test19crac1 bin]$ sqlldr -help
SQL*Loader: Release 19.0.0.0.0 - Production on Wed Aug 30 00:22:53 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, 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 250, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 1048576)
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 conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
partition_memory -- direct path partition memory limit to start spilling (kb) (Default 0)
table -- Table for express mode load
date_format -- Date format for express mode load
timestamp_format -- Timestamp format for express mode load
terminated_by -- terminated by character for express mode load
enclosed_by -- enclosed by character for express mode load
optionally_enclosed_by -- optionally enclosed by character for express mode load
characterset -- characterset for express mode load
degree_of_parallelism -- degree of parallelism for express mode load and external table load
trim -- trim type for express mode load and external table load
csv -- csv format data files for express mode load
nullif -- table level nullif clause for express mode load
field_names -- field names setting for first record of data files for express mode load
dnfs_enable -- option for enabling or disabling Direct NFS (dNFS) for input data files (Default FALSE)
dnfs_readbuffers -- the number of Direct NFS (dNFS) read buffers (Default 4)
sdf_prefix -- prefix to append to start of every LOB File and Secondary Data File
help -- display help messages (Default FALSE)
empty_lobs_are_null -- set empty LOBs to null (Default FALSE)
defaults -- direct path default value loading; EVALUATE_ONCE, EVALUATE_EVERY_ROW, IGNORE, IGNORE_UNSUPPORTED_EVALUATE_ONCE, IGNORE_UNSUPPORTED_EVALUATE_EVERY_ROW
direct_path_lock_wait -- wait for access to table when currently locked (Default FALSE)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

SQL*Loader 控制文件

SQL*Loader 要将数据加载到 Oracle 数据库中，SQL*Loader控制文件是必不可少的，它是用 SQL*Loader 理解的语言编写的文本文件。

控制文件告诉 SQL*Loader 在何处查找数据、如何分析和解释数据、在何处插入数据等。

通常，控制文件有三个主要部分，顺序如下：

• 会话范围的信息
• 表和字段列表信息
• 输入数据（可选部分）

要记住的一些控制文件语法注意事项包括：

• 语法是自由格式的（语句可以跨多行扩展）。
• 语法不区分大小写;但是，用单引号或双引号括起来的字符串按字面意思，包括大小写。
• 在控制文件语法中，注释从两个连字符 （） 扩展而来 这标志着注释的开头到行尾。可选 控制文件的第三部分被解释为数据而不是控件文件语法;因此，不支持本节中的注释。--
• 关键字对SQL*Loader 具有特殊含义，因此是保留的。为避免潜在的冲突，Oracle 建议您不要将 或 用作任何表或列的名称。CONSTANTZONECONSTANTZONE

SQL*Loader程序从控制文件中指定的一个或多个数据文件（或文件的操作系统等效项）读取数据。

从 SQL*Loader 的角度来看，数据文件中的数据被组织为记录。特定数据文件可以是固定记录格式、可变记录格式或流记录格式。可以使用参数在控制文件中指定记录格式。如果未指定记录格式，则默认为流记录格式。

使用 sqlldr，将 emp1.dat 导入到 scott 下的普通表 emp1

建立控制文件

[oracle@oracle dir1]$ vi /home/oracle/dir1/emp1.ctl load data

infile '/home/oracle/dir1/emp1.dat' insert

into table emp1

fields terminated by ',' (empno,ename,sal,comm,deptno)

在 scott 下建立 emp1 表结构（内部表）

SCOTT@PROD>create table emp1 as select * from emp1_ext where 1=2;

ORACLE_LOADER 引擎导入（normal 方式）

[oracle@oracle ~]$cd /home/oracle/dir1

[oracle@oracle dir1]$ ll

[oracle@oracle dir1]$ sqlldr

[oracle@oracle dir1]$ sqlldr scott/tiger control=emp1.ctl log=emp1.log data=emp1.dat

验证结果

SCOTT@PROD>select * from emp1;

数据源和控制文件合并在.ctl 里描述

[oracle@oracle dir1]$ vi emp2.ctl 

load data

infile * 

append

into table emp1

fields terminated by ',' optionally enclosed by '"' (empno,ename,sal,comm,deptno) 

begindata

8888,Jieke,1000,,"10"

9999,ORACLE,8000,100,20

[oracle@oracle dir1]$ sqlldr scott/tiger control=emp2.ctl log=emp2.log

官方文档链接：

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-concepts.html#GUID-F99250F9-F720-45EE-8159-9B8A8E5A77D6

全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~

欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
CSDN ：https://blog.csdn.net/JiekeXu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————