SQL loader命令及参数

  • 2017-12-20
  • 711
  • 0
★ sqlldr命令语法

在Windows下,SQL*LOADER的命令为SQLLDR,在UNIX下一般为sqlldr/sqlload。
sqlldr语法: SQLLOAD 关键字 = 值 [,keyword=value,…]
有效的关键字:
  • USERID:ORACLE username/password:Oracle用户名及口令,如果在命令中没有给出,Oracle会提示你输入用户名及口令,一般在sqlldr命令就直接输入用户名,加斜杠,接着输入口令
  • CONTROL:Control file name:控制文件名, 一般文件类型为.ctl
  • LOG: Log file name:日志文件名。记录加载过程中的信息,如果没有给出日志文件名,则Oracle会自动建立一个和控制文件同名的,但是类型为.log的文件
  • BAD: Bad file name:坏信息文件名。记录不符要求的信息。如果没有有给出坏文件名,则Oracle自动建立一个与控制文件名相同,但是类型为.bad的文件
  • DATA:Data file name:数据文件名,如果没有给出数据文件名,则Oracle认为数据文件名与控制文件名相同,但是类型是.dat的文件
  • DISCARD:Discard file name:Oracle既不会拒绝也不插入到数据库的记录中的文件。因为们不符合控制文件中的where字句的条件要求或全部为空。只要你给出名字,Oracle就创建该文件,如果没有给出名字,Oracle就会自动创建一个与控制文件相同,但是类型为.dsc的文件
  • DISCARDMAX:Number of discards to allow (Default all):停止加载前允许丢弃的记录最大数。如果没有给出这个参数,那么所有的记录都可以被丢弃
  • SKIP:Number of logical records to skip (默认0):开始加载前需要效果的xx个逻辑记录数,缺省是0
  • LOAD:Number of logical records to load (全部默认):要加载的最大记录数。没有给出这个记录则表示要加载所有的记录
  • ERRORS:Number of errors to allow (默认50):允许加载汇总出现的错误数,缺省是50
  • ROWS:Number of rows in conventional path bind array or between direct path data saves(默认: 常规路径 64, 所有直接路径):数组处理时的行数,如果没有指定ROW,缺省为64【说白了就是多少行提交一次】
  • BINDSIZE:Size of conventional path bind array in bytes(默认65536):数组大小(字节数)最大字节与OS有关,大小有ROWS确定能
  • SILENT:Suppress messages during run (header,feedback,errors,discards,partitions):运行中特性的信息显示
    • feedback:抑制每个提交点的反馈信息
    • errors:抑制登录引起的Oracle错误的每条信息
    • Discards:抑制登录丢弃的每条记录
    • Partitions:抑制分区显示的信息
    • ALL:抑制上边所有的信息
  • DIRECT:DIRECT specifies the data path, that is, the load method to use, either conventional path or direct path. A value of true specifies a direct path load. A value of false specifies a conventional path load.【Default: false】:direct指出了数据加载方式,要么是直接路径加载,要么是常规路径加载,默认是false,也就是常规路径加载
  • PARFILE:parameter file: name of file that contains parameter specifications:包含指定参数的参数文件名
  • PARALLEL:do parallel load (默认FALSE):是否并行load,默认为否
  • FILE:FILE specifies the database file to allocate extents from. It is used only for direct path parallel loads. By varying the value of the FILE parameter for different SQL*Loader processes, data can be loaded onto a system with minimal disk contention.【Default: none】:FILE参数指定加载的数据的表中间数据文件的名字,该参数仅仅用于直接路径并行加载,为不同SQL*Loader直接指定不同的文件名字,可以有最小化磁盘争用
  • SKIP_UNUSABLE_INDEXES
    • Default: The value of the Oracle database configuration parameter, SKIP_UNUSABLE_INDEXES, as specified in the initialization parameter file. The default database setting is TRUE. Both SQL*Loader and the Oracle database provide a SKIP_UNUSABLE_INDEXES parameter. The SQL*Loader SKIP_UNUSABLE_INDEXES parameter is specified at the SQL*Loader command line. The Oracle database SKIP_UNUSABLE_INDEXES parameter is specified as a configuration parameter in the initialization parameter file. It is important to understand how they affect each other.If you specify a value for SKIP_UNUSABLE_INDEXES at the SQL*Loader command line, then it overrides the value of the SKIP_UNUSABLE_INDEXESconfiguration parameter in the initialization parameter file.If you do not specify a value for SKIP_UNUSABLE_INDEXES at the SQL*Loader command line, then SQL*Loader uses the database setting for the SKIP_UNUSABLE_INDEXES configuration parameter, as specified in the initialization parameter file. If the initialization parameter file does not specify a database setting for SKIP_UNUSABLE_INDEXES, then the default database setting is TRUE.:Oracle数据库本身也有一个SKIP_UNUSABLE_INDEXES参数,默认true,如果在 sqlldr里指定了该参数,那么就会覆盖Oracle库的设定,否则,sqlldr就使用Oracle库的参数设定
    • A value of TRUE for SKIP_UNUSABLE_INDEXES means that if an index in an Index Unusable state is encountered, it is skipped and the load operation continues. This allows SQL*Loader to load a table with indexes that are in an Unusable state prior to the beginning of the load. Indexes that are not in an Unusable state at load time will be maintained by SQL*Loader. Indexes that are in an Unusable state at load time will not be maintained but will remain in an Unusable state at load completion.如果SKIP_UNUSABLE_INDEXES参数是默认的true,那么如果SQL*Loader处理的表有一些unusable的index,SQL*Loader不会去处理,也不去维护,他会继续load数据,SQL*Loader只会维护那些状态正常的index,那些unusable的的index在SQL*Loader装载数据后,依然是unusable的状态
  • SKIP_INDEX_MAINTENANCE:The SKIP_INDEX_MAINTENANCE parameter stops index maintenance for direct path loads but does not apply to conventional path loads. It causes the index partitions that would have had index keys added to them to be marked Index Unusable instead, because the index segment is inconsistent with respect to the data it indexes. Index segments that are not affected by the load retain the Index Unusable state they had before the load.【Default: false】使用了这个参数, 并且是直接路径加载的话,那么在SQL*Loader过程中,就不会去维护索引,也就意味着,索引会被置为unusable
  • READSIZE :Size of Read buffer (默认1048576)
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.命令行参数可以由位置或关键字指定。前者的例子是 ‘sqlload scott/tiger foo’;后者的例子是 ‘sqlload control=foo userid=scott/tiger’.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,’SQLLOAD SCott/tiger control=foo logfile=log’, 但’不允许 sqlload scott/tiger control=foo log’,即使允许参数 ‘log’ 的位置正确
SQL*Loader控制文件语法

• 控制文件样例如下:

• 样例解释如下:

  • 1、 –的含义是就是注释,只要是–后边的,都不会被执行和解释
  • 2、OPTIONS (skip=1,rows=128),意思是第一行跳过读取和load入,每128行的load提交一次(commit),关于OPTIOINS,其实是有部分sqlldr的选项,可以在控制文件里边指定(当然,如果sqlldr指定了同名的选项的话,就会覆盖控制文件里的参数),根据Oracle的books,OPTIONS里可用的选项如下:
    • BINDSIZE = n
    • COLUMNARRAYROWS = n
    • DATE_CACHE = n
    • DIRECT = {TRUE | FALSE}
    • ERRORS = n
    • EXTERNAL_TABLE = {NOT_USED | GENERATE_ONLY | EXECUTE}
    • FILE
    • LOAD = n
    • MULTITHREADING = {TRUE | FALSE}
    • PARALLEL = {TRUE | FALSE}
    • READSIZE = n
    • RESUMABLE = {TRUE | FALSE}
    • RESUMABLE_NAME = ‘text string’
    • RESUMABLE_TIMEOUT = n
    • ROWS = n
    • SILENT = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}
    • SKIP = n
    • SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
    • SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
    • STREAMSIZE = n
  • 3、 LOAD DATA:告诉SQL*Loader这是一次新数据的加载,具体关于LOAD选项,是有很多的可选项分支的,具体见如下【其实看了语法以后会发现,唯一必选的就是一个LOAD或者是CONTINUE_LOAD】:
  • 4、INFILE ‘sample.dat’,指定包含数据的文件的名字,如果包含数据的文件有多个,那么就写多行的INFILE来指定,如果需要load的数据也是包含在控制文件中,就使用 INFILE *来表示
    • 语法如下:

1

    • 举例如下,通过如下的举例,我们知道,每个INFILE,都可以指定BADFILE、DISCADRFILE等参数:

  • 5、BADFILE ‘sample.bad’,这里的BADFILE可以是绝对路径,也可以是相对路径,这里指定BADFILE的作用和sqlldr里指定BAD的含义一致
  • 6、DISCARDFILE ‘sample.dsc’,这里的DISCARDFILE可以是绝对路径,也可以是相对路径,它的作用等同于sqlldr里的DISCARD,另外还有一个参数就是DISCARDMAX,等同于sqlldr里的含义
    • 语法如下:
1
  • 7、APPEND,指示数据以何种方式插入,这里有三种可选项:
    • insert–为缺省方式,在数据装载开始时要求表为空
    • append–在表中追加新记录
    • replace–删除旧记录(用 delete from table 语句),替换成新装载的记录
    • truncate–删除旧记录(用 truncate table 语句),替换成新装载的记录
  • 8、INTO TABLE emp,指出数据要插入到哪张表中去,这里是插入到emp表中去
    • 语法如下:
1
  • 9、WHEN (57) = ‘.’,用 when 子句选择导入符合条件的记录,这里意思是,当第57个字段的值是个“.”的时候,这样的数据才符合要求
    • 语法如下:
1
    • 示例如下:
WHEN (5) = ‘q’
→ 解释:the following clause indicates that any record with the value “q” in the fifth column position should be loaded
WHEN (deptno = ’10’) AND (job = ‘SALES’)
→ 解释:A  WHEN clause can contain several comparisons, provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND.
  • 10、Fields terminated by “|”,如果数据文件中,字段的分隔符号都是一致的,那么可以使用FIelds选项指定分隔符
    • 语法如下:
2
    • 如果去掉Fields字段,剩下的termination_spec和ENCLOSED字段可以在“字段参考列表”中针对每一行单独特别设定,具体的示例下边再讲。那里的用法和这里唯一的区别在于,那里没有Fields字段
  • 11、TRAILING NULLCOLS,表的字段没有对应的值时允许为空
  • 12、字段列表参考,这里是用来映射字段和需要load的数据的列之间的对应关系的,这个最重要,下边单独了一个章节来讲
[略,下详]
★ 控制文件字段参考列表SQL*Loader Field List Reference

• 先祭出实例【注】:以下示例是控制文件的最后一部分,拿括号括起来的那一部分:

• 样例讲解:
  • 1、hiredate SYSDATE, → 含义是,hiredata字段装载数据的时候,这里的字段直接使用当前系统时间
  • 2、deptno POSITION(1:2) INTEGER EXTERNAL(2) NULLIF deptno=BLANKS, → deptno是字段的名称,剩下的要分三个部分讲
    • POSITION(1:2),如果使用POSITION,那意思是,数据文件没有分隔符,只好靠位置来拆封,要从数据文件加载数据,SQL * Loader必须知道字段的长度和位置。该参数针对位置指示可以是绝对的,也可以是相对的。 POSITION的参数必须用圆括号括起来。即使字符长度语义用于数据文件,开始,结束和整数值也始终以字节为单位。
      • 语法解释如下:
12
      • 样例如下:
DEPTNO position(1:2),
→ 指定截取数据文件一行记录的第1个字符到第2个字符作为DEPTNO列的列值。
DNAME position(*+1:13),
→ 指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置。即截取数据文件一行记录的第4个字符到第13个字符作为DNAME列的列值。
LOC position(*+1:22),
→ 指定从上个字段的结束位置开始偏移1个字符的位置作为本列数据的开始位置。即截取数据文件一行记录的第15个字符到第22个字符作为LOC列的列值
    • INTEGER EXTERNAL(2),指示deptno字段的数据类型,这里在SQL*Loader中可以指定的数据类型有如下.另外还要指出,控制文件的数据类型和Oracle数据库里的数据类型是不同的,A data field defined as CHAR in the control file simply tells SQL*Loader how to create the row insert. The data could then be inserted into a CHAR, VARCHAR2, NCHAR, NVARCHAR2, or even a NUMBER or DATE column in the database, with the Oracle database handling any necessary conversions.
      • CHAR → 如果指定数据类型,默认是就是CHAR,长度是255,如果你要搞入的数据比255长,那就必须要手动指定长度[标红比较常用]
      • Datetime and Interval
      • GRAPHIC
      • GRAPHIC EXTERNAL
      • Numeric EXTERNAL (INTEGER, FLOAT, DECIMAL, ZONED)
      • RAW
      • VARCHARC
      • VARRAWC
    • NULLIF deptno=BLANKS,NULLIF:如果符合条件则设为NULL,该例子的含义是:字段为BLANKS,则字段为NULL,另外,还有DEFAULTIF。DEFAULTIF,如果DEFAULT IF所该约束的字段是一个数据类型的字段,那么满足条件后,default的值是0,如果是一个非数字类型的字段,那么满足条件后,default的值是NULL。针对DEFAULTIF来说,“数据类型的字段”代表如下一些:
      • BYTEINT
      • SMALLINT
      • INTEGER
      • FLOAT
      • DOUBLE
      • ZONED
      • (packed) DECIMAL
      • Numeric EXTERNAL (INTEGER, FLOAT, DECIMAL, and ZONED)
  • 3、job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS “UPPER(:job)”
    • TERMINATED BY WHITESPACE,字段间分隔的符号
      • 语法如下:
12
      • 示例如下:
TERMINATED BY ‘,’ → 对应分隔数据样例→ a data string, ENCLOSED BY ‘”‘ → 对应分隔数据样例→ “a data string” TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ → 对应分隔数据样例→ “a data string”, ENCLOSED BY ‘(‘ AND ‘)’ → 对应分隔数据样例→ (a data string)
  • 4、comm INTEGER EXTERNAL ENCLOSED BY ‘(‘ AND ‘%’ “:comm * 100”
    • ENCLOSED BY ‘(‘ AND ‘%’,含义是字段是由(和%括起来的
    • “:comm * 100″,双引号括起来的,可以放一个函数,比如”UPPER(:job)”,也可以是一个表达式,比如这里的”:comm * 100″ ,其中字段名称都是以冒号加字段名字组成。

评论

还没有任何评论,你来说两句吧

津公网安备 12010302001147号 津ICP备18003766号