SQL*Loader使用方法

--=====================

-- SQL*Loader使用方法

--=====================

一、SQL*Loader的体系结构

SQL*Loader由一个输入控制文件来控制整个装载的相关描述信息,一个或多个数据文件作为原始数据,其详细组成结构包括

Input Datafiles -->装载到数据库的原始数据文件

Loader Control file -->提供给QL*Loader寻找及翻译数据的相关信息

Log File -->装载过程中产生的日志信息

Bad Files -->被剔除的一些不合乎规范化的数据,由SQL*Loader剔除,也可能被Oracle剔除

Discard Files -->对不满足控制文件中记录选择标准的一些物理记录

以上五个完整的部分最终将数据导入到数据库,当然,部分组件可以省略。

二、控制文件的作用及组成

控制文件是一个文本文件,控制文件中记录的信息告诉SQL*Loader在哪里寻找数据、如何翻译数据,以及将数据插入到哪里等

控制文件的组成分为三个部分

第一部分主要是关于通外部会话的相关信息

如一些全局选项、行信息、是否跳过特殊记录等

infile子句指明了从哪里寻找源数据

第二部分由一个或多个Into table块,每一个块包含一些被导入表的相关信息,如表名,列名等

第三部分为可选项,如果存在则包含导入的源数据

控制文件写法的注意事项

语法结构自由

不区分大小写

在行开始处使用--来作为注释行,在控制文件中的第三部分使用--来注释不被支持

关键字constant 和zone被保留

三、数据文件

数据文件可以有多个,这些数据文件需要在控制文件中指定

从SQL*Loader角度来看,数据文件中的数据被当做一条条记录

一个数据文件描述数据文件记录有三种可选的格式

固定记录格式

可变记录格式

流记录格式

这些记录格式在控制文件使用infile参数时,如果记录的格式未指定,则缺省的为流记录格式。如使用infile *时则为流记录格式

下面给出几种不同记录格式的例子

a.固定格式:INFILE datafile_name "fix n"

load data

infile 'example.dat' "fix 11" --表明每条记录长度固定为个字节

into table example

fields terminated by ',' optionally enclosed by '"'

(col1, col2)

example.dat:

001, cd, 0002,fghi, --第一条记录为, cd, 第二条记录为,fghi, 其中第二条记录包含了一个换行符

00003,lmn,

1, "pqrs",

0005,uvwx,

b.可变格式:INFILE "datafile_name" "var n"

load data

infile 'example.dat' "var 3" --使用3位来描述一条记录的长度

into table example

fields terminated by ',' optionally enclosed by '"'

(col1 char(5),

col2 char(7))

example.dat:

009hello,cd,010world,im, --009 表明第一条记录的长度为个9字节,表明第二条记录的长度为10个字节等

012my,name is,

c.流记录格式:INFILE datafile_name ["str terminator_string"]

load data

infile 'example.dat' "str '|/n'" --使用| 或换行符来作为一条记录的终止

into table example

fields terminated by ',' optionally enclosed by '"'

(col1 char(5),

col2 char(7))

example.dat:

hello,world,|

james,bond,|

逻辑记录的概念

通常情况下,数据文件中的一条物理记录即是一条逻辑记录,即数据文件的一条记录对应于数据库的一条记录SQL*Loader扩展了该功能可以将多条物理记录形成一条逻辑记录,而由这个组合再来生成一条数据库中的记录

SQL*Loader支持两种策略来形成逻辑记录

组合固定条数的物理记录来形成逻辑记录

将满足特定条件的物理记录组合并形成逻辑记录

四、数据文件装载方式

1.传统路径导入

使用生成SQL Insert语句来处理源数据,并且通过commit提交保存数据。每次数据导入将产生一些事务

在插入数据时寻找可用数据块,然后将数据填充到数据块

在插入到分区表的单个分区时使用下面的语法

INSERT INTO TABLE T PARTITION (P) VALUES ...

基于多cpu系统使用多个装载会话执行并发。即将数据文件分割为多个来装载

2.直接路径导入

直接将数据写到Oracle数据文件,并更所使用块的高水位线标记来保存数据

支持数据的并行导入

直接路径导入期间,数据转换发生在客户端而非服务器端。即位于服务器端参数文件中NLS参数不会被使用

可以通过在控制文件中设置NLS参数或设置服务器端合适的环境变量,如下面的例子

HIREDATE DATE 'YYYYMMDD' --为控制文件中的HIREDATE指定格式

% export NLS_DATE_FORMAT='YYYYMMDD' --在服务器端设定NLS_DATE_FORMAT

直接路径装载单个分区或子分区,装载期间的其它分区可以执行DML操作

LOAD INTO TABLE T PARTITION (P) VALUES ...

LOAD INTO TABLE T SUBPARTITION (P) VALUES ...

使用直接路径装载时,需要指定DIRECT=true

支持两种不同的并发

1.同时装载到分区表表的不同分区或同时装载到不同的表

2.分成多个服务器装载到分区表的单个分区或单个表,最后将装载的临时段合并保存到分区或表

3.两者对比(下面描述中传统导入方式称为前者,直接导入方式称为后者)

a.前者使用commit来保存数据,后者更新高水位线标记保存数据

b.前者产生redo记录,后者基于特定的条件产生redo记录

c.前者强制所有的约束,后者仅仅强制primary key,unique,not null约束

d.前者将触发insert触发器,后者不会触发insert触发器

e.前者支持簇表,后者不支持簇表

f.前者插入数据时其它用户可以DML表,后者则不行

五、演示SQL*Loader

1.SQL*Loader可执行程序(sqlldr)所在的位置

[oracle@oradb ~]$ ls -lh $ORACLE_HOME/bin/sql*

-rwxr-x--x 1 oracle oinstall 634K Sep 13 20:01 /u01/app/oracle/10g/bin/sqlldr

2.查看sqlldr的帮助信息,

[oracle@oradb ~]$ sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 10:38:31 2010

Copyright (c) 1982, 2005, Oracle. 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 (Default NOT_USED)

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)

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.

3.将数据文件和控制文件组合在一起

[oracle@oradb ~]$ cat sqlldr_demo/demo1.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

BEGINDATA

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

SQL> show user;

USER is "ROBINSON"

SQL> create table dept

2 (deptno number(2) constraint dept_pk primary key,

3 dname varchar2(20),

4 loc varchar2(20));

Table created.

[oracle@oradb sqlldr_demo]$ sqlldr robinson/robinson control=demo1.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 11:29:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 4

SQL> select * from dept;

DEPTNO DNAME LOC

---------- -------------------- --------------------

10 Sales Virginia

20 Accounting Virginia

30 Consulting Virginia

40 Finance Virginia

4.将数据文件和控制文件分离实现数据装载

[oracle@oradb sqlldr_demo]$ cat demo1.ctl demo1.data --查看分离后两者的内容

LOAD DATA

INFILE demo1.data

INTO TABLE DEPT

FIELDS TERMINATED BY ','

(DEPTNO, DNAME, LOC )

10,Sales,Virginia

20,Accounting,Virginia

30,Consulting,Virginia

40,Finance,Virginia

[oracle@oradb sqlldr_demo]$ sqlldr robinson/robinson control=demo1.ctl log=demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 12:21:35 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-601: For INSERT option, table must be empty. Error on table DEPT

SQL> truncate table dept; --收到了SQL*Loader-601错误提示,清空原表

[oracle@oradb sqlldr_demo]$ sqlldr robinson/robinson control=demo1.ctl log=demo1.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Sep 23 12:22:39 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 4 --再次正常导入

六、更多参考

Oracle 冷备份

SPFILE错误导致数据库无法启动

Oracle 用户、对象权限、系统权限

Oracle 角色、配置文件

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle 控制文件(CONTROLFILE)

Oracle 表空间与数据文件

Oracle 归档日志

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Spark学习技巧

实战phoenix

一 安装部署 1, 下载 http://archive.apache.org/dist/phoenix/ 本文下载的是apache-phoenix-4.12....

37510
来自专栏云计算教程系列

想熟悉PostgreSQL?这篇就够了

PostgreSQL是自由的对象-关系型数据库服务器,在灵活的BSD风格许可证下发行。它在其他开放源代码数据库系统和专有系统之外,为用户又提供了一种选择。 我们...

2182
来自专栏逸鹏说道

SQL Server 使用全文索引进行页面搜索

概述 全文引擎使用全文索引中的信息来编译可快速搜索表中的特定词或词组的全文查询。全文索引将有关重要的词及其位置的信息存储在数据库表的一列或多列中。全文索引是一...

3647
来自专栏互联网杂技

SQL注入攻防入门详解

毕业开始从事winfrm到今年转到 web ,在码农届已经足足混了快接近3年了,但是对安全方面的知识依旧薄弱,事实上是没机会接触相关开发……必须的各种借口。这几...

52710
来自专栏游戏杂谈

node-mysql操作transactions的实例

如果执行commit后,再执行node.js脚本(执行的是一句更新sql--UPDATE user_info SET userName = ‘张一’ WHERE...

1251
来自专栏数据库新发现

关于shared pool的深入探讨(五)

http://www.eygle.com/internal/shared_pool-5.htm

1212
来自专栏大内老A

创建代码生成器可以很简单:如何通过T4模板生成代码?[上篇]

在《基于T4的代码生成方式》中,我对T4模板的组成结构、语法,以及T4引擎的工作原理进行了大体的介绍,并且编写了一个T4模板实现了如何将一个XML转变成C#代码...

7398
来自专栏MYSQL轻松学

MYSQL8.0.11二进制安装

https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.t...

1180
来自专栏乐沙弥的世界

RMAN 提示符下执行SQL语句

       在手动恢复数据库时,有时候需要在SQL*Plus提示符以及操作系统提示符,RMAN提示符下来回切换显得有些繁琐。实际上RMAN为我们提供了命令行下...

1103
来自专栏乐沙弥的世界

共享池的调整与优化(Shared pool Tuning)

--=======================================

1002

扫码关注云+社区

领取腾讯云代金券