Oracle sqlldr数据加载

1 sqlldr

传统路径：sqlldr会利用sql插入为我们加载数据

直接路径加载：sqlldr不适用sql，直接格式化数据块，绕开undo，避开redo，最快的方法就是并行直接路径加载

sqlldr只是一个命令行工具，并非一个api，在plsql中不能调用

2 sqlldr体系结构

2.1 控制部分

LOAD DATA—告诉sqlldr做什么，可以用

INFILE *

INTO TABLE BONUS

Insert-----默认

FIELDS TERMINATED BY ","

(ENAME,JOB,SAL)

BEGINDATA

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891

WARD,SALESMAN,3128

KING,PRESIDENT,2523

--

LOAD DATA—告诉sqlldr做什么，可以用CONTINUE_LOAD来继续加载

infile *——表示数据文件的位置，为*表示数据文件在控制文件ctl中。如果是一个路径，表示数据与控制文件分离的。

into table bonus——表示插入表bonus，该表在sqlldr命令执行前就已经创建好。

into还有些参数：insert ：向表中插入数据，此表必须为空，默认的参数insert

append：向表中追加数据，不管表中是否有数据

replace：替换表中数据，相当于先delete在insert

truncate：先truncate表中数据，在insert

Fields terminated by ‘，’表示数据部分的分隔符是逗号，，也可以替换成其他任何可见字符

(ENAME,JOB,SAL) 要插入表的列名

Bingdata 表示以下为加载的数据，当infile 为*有效

OPTIONALLY ENCLOSED BY 指明定界符

2.2 日志部分

sqlldr在默认情况下，会在sqlldr执行过程中，产生一个与控制文件同名的日志文件，。Log，日志文件中记录了加载数据的各项统计信息，

错误文件，在加载过程中，由于数据不符合规范就会生成一个与控制文件同名的错误文件，

废弃文件，。Dsc默认不会有

3 加载数据及常见问题

sqlldr userid=/ control=demo1.ctl

sqlldr userid/987064@orcl control=demo1.ctl

3.1 加载一个excel文件

将excel文件另存为csv格式的文件

然后控制文件中

LOAD DATA

INFILE 'F:\sqlldr\1024TEST.csv'  --指定文件名

BADFILE 'F:\sqlldr\1024TEST.bad'

3.2 加载的文件不是逗号分开

1 可以修改数据文件，将其他分隔符替换为逗号

2 修改控制文件，FIELDS TERMINATED BY ","，","修改为实际的符号

3.3 要加载的数据中包含分隔符

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(DEPTNO, DNAME, LOC )

BEGINDATA

10,Sales,"Virginia,USA"---"Virginia,USA" 及一个字段

结果就是 10 Sales Virginia,USA

20 Accounting Va, "USA"

OPTIONALLY ENCLOSED BY 指明定界符

3.4 数据文件中没有分隔符

数据文件中没有分隔符，在控制文件中

(

ENAME position(1:5),

JOB position(7:15),

SAL position(17:20)

)    用position关键字来指定列的起始结束位置，比如JOB position(7:15)，job从第7个字符开始到第15个字符截止，

position可以position(*+2:15),用*相对偏移量，上次从哪里结束，下个字段就从哪里开始

postion(*)char(9)

3.5 数据文件中的列要比导入到表的列少

SQL> desc dept

Name                                      Null?    Type

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

DEPTNO                                    NOT NULL NUMBER(2)

DNAME                                              VARCHAR2(14)

LOC                                                VARCHAR2(13)

表dept有3个列

控制文件

LOAD DATA

INFILE ldr_case3.dat

TRUNCATE INTO TABLE dept

(

ENAME position(1:5),

JOB position(7:15),

SAL "0") 在控制文件中增加一列

或者

SAL “substr(:job,1,1)”

3.6 数据文件中的列比表中多

在控制文件中可以用FILLER指定过滤列。

(

ENAME position(1:6),

TCOL FILLER position(8:11),

JOB position(13:21),

SAL position(23:26)）

如果数据文件不是定长格式，而是通过分隔符来处理的，

FIELDS TERMINATED BY ","

(ENAME,TCOL FILLER,JOB,SAL) 过滤了数据文件中的第二列？

3.7 提供了多个数据文件要导入到同一张表

LOAD DATA

INFILE ldr_case8_1.dat

INFILE ldr_case8_2.dat

INFILE ldr_case8_3.dat

3.8 同一个数据文件导入到不同的表

LOAD DATA

INFILE ldr_case9.dat

DISCARDFILE ldr_case9.dsc

TRUNCATE

INTO TABLE BONUS

WHEN TAB='BON'

(TAB FILLER POSITION(1:3),

ENAME POSITION(5:9) ,

JOB POSITION(*+1:18),

SAL POSITION(*+1)

)

INTO TABLE MANAGER

WHEN TAB = 'MGR'

(TAB FILLER POSITION(1:3),

MGRNO POSITION(4:5) ,

MNAME POSITION(7:13),

JOB POSITION(*+1))

指定了when关键字，when逻辑判断不知道or，连接条件只能有and，不支持or

When字句不是使用区间大于或小于，没有or，没有is null等

3.9 数据文件的前N行不想导入

sqlldr userid/987064@orcl control=demo1.ctl skip=N

sqlldr userid/987064@orcl control=demo1.ctl skip=4 LOAD=6及导入4到9行

3.10 要加载的数据文件中有换行符

Windows下回车+换行 chr(13)+chr(10),linux chr(10)

1 手工指定换行符

LOAD DATA

INFILE ldr_case11_1.dat

TRUNCATE INTO TABLE MANAGER

FIELDS TERMINATED BY ","

(MGRNO,

MNAME,

JOB,

REMARK "replace(:remark,'\\n',chr(10))"

)

2 指定FIX属性来处理换行符（定长数据专用）

10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.

Ctl

LOAD DATA

INFILE ldr_case11_2.dat "fix 68"—包含换行在内的68个字符

TRUNCATE INTO TABLE MANAGER

(

MGRNO position(1:2),

MNAME position(*+1:10),

JOB position(*+1:24),

REMARK position(*+1:65)

)

3 用var来处理换行

LOAD DATA

INFILE ldr_case11_3.dat "var 3" 通过var属性来指定每行开头固定的字符串长度

4 指定str属性来处理换行

10,SMITH,SALES MANAGER,This is SMITH.

He is a Sales Manager.|

INFILE ldr_case11_4.dat "str '|\r\n'"

Windows 中 select utl_raw.cast_to_raw( '|'||chr(10) ) from dual;

Ctl

INFILE demo.dat "str X'7C0A'"

3.11 导入的字段包含lob

1 数据文件保存在控制文件中

先修改表的列类型为clob

(MGRNO, MNAME, JOB, REMARK char(100000)) 指定列的长度，

2 数据文件保存在独立的文件中

create table lob_demo

2  ( owner      varchar2(255),

3    time_stamp date,

4    filename   varchar2(255),

5    data       blob

6  )

Ctl

LOAD DATA

INFILE *

REPLACE

INTO TABLE LOB_DEMO

( owner       position(17:25),

time_stamp  position(44:55) date "Mon DD HH24:MI",

filename    position(57:100),

data        LOBFILE(filename) TERMINATED BY EOF

)

BEGINDATA

-rw-r--r--    1 tkyte    tkyte     1220342 Jun 17 15:26 classes12.zip

3.12 某些字段为null报错

FIELDS TERMINATED BY "," TRAILING NULLCOLS

当某行对应的列没有值时，sqlldr自动赋值为null，而不是报错

3.13 导入日期格式

lOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY ','

(DEPTNO,

DNAME,

LOC,

LAST_UPDATED date 'dd/mm/yyyy'

)

BEGINDATA

10,Sales,Virginia,1/5/2000

20,Accounting,Virginia,21/6/1999

LAST_UPDATED date 'yyyy-mm-dd hh24:mi:ss'

3.14 如何使用函数加载数据

FIELDS TERMINATED BY ','

(DEPTNO,

DNAME        "upper(:dname)",

LOC          "upper(:loc)",

TRAILING NULLCOLS

(DEPTNO,

DNAME        "upper(:dname)",

LOC          "upper(:loc)", ---loc “222”该列所有值都替换成222

LAST_UPDATED

"case

when length(:last_updated) > 9

then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy')

when instr(:last_updated,':') > 0

then to_date(:last_updated,'hh24:mi:ss')

else to_date(:last_updated,'dd/mm/yyyy')

end"

append

INTO TABLE BULK_NUMBERS

FIELDS TERMINATED BY ','

Optionally enclosed by '"'

trailing nullcols

(id ,

a  "substr(upper(:a),1,2)" ,--- a  "replace(:a,:a,'000111111')",字符串类型

b "replace(:b,:b,111111)",

c ,

DATE1 date "MM-DD-YYYY HH24:MI:SS")

----------------b "replace(:b,:b,111111)"

-----------------b "222"    a  " '000222' ",单引号跟双引号之间有空格

LOAD DATA

INFILE Book1.csv

APPEND INTO TABLE ruoxitest

FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(ENAME,

JOB "lower(:job)",

sal "to_number(:sal)"

)

LOAD DATA

APPEND INTO TABLE RUOXITEST

FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'

(ENAME,JOB,SAL)

BEGINDATA

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891

WARD,SALESMAN,3128

KING,PRESIDENT,2523

3.15 Sqlldr出现704+ora 12514错误

在F:\oracle\product\10.2.0\client_2\NETWORK\ADMIN的tns文件中，指定了相同的service name的监听路径。

Record 3: Rejected - Error on table RUOXITEST, column TEST.

ORA-01722: invalid number

[root@localhost oracle]# dos2unix Book1.csv

dos2unix: converting file Book1.csv to UNIX format ...

[oracle@localhost oracle]$ sqlldr scott/987064@grs control=case1.ctl

成功

4 加载大量数据

4.1 增加errors参数

>sqlldr scott/cxxxx@orcl control=xxxxx.ctl errors=10

明确指定出现错误到10次就停止加载

4.2 指定rows参数

sqlldr常规路径导入默认是一次 64行，可以适当增加rows

rows=640

有可能rows的值超过了bindsize的值，bingsize的默认值256K，

>sqlldr scott/cxxxx@orcl control=xxxxx.ctl errors=10 rows=5000 bindsize=10485760

Bindsize 10M(1024*1024*10) =10485760

4.3使用直接路径加载direct

>sqlldr scott/cxxxx@orcl control=xxxxx.ctl direct=true

直接路径加载默认是读取全部记录，不需要rows参数，

直接路径主要有2个参数：

streamsize 读取到的数据存入流缓存区

streamsize 10M(1024*1024*10) =10485760

date_cache指定一个转换后日期格式的缓冲区，以条为单位，默认1000，如有有导入的日期列

date_cache =5000

>sqlldr scott/cxxxx@orcl control=xxxxx.ctl direct=true Streamsize=10485760 date_cache=5000

5 外部表加载数据

5.1  建外部表

外部表导数据的限制：数据文件必须在服务器上，或在服务器*问的输入文件。

多个用户并发的使用相同的外部表来处理不同的输入文件

sqlldr scott/xx#orcl demo1.ctl external_table=generate_only

External_table有3个参数值，

Not_used,默认值

Execute,说明sqlldr不会生成并行执行一个sql insert语句，而是会创建一个外部表，并使用一个批量sql语句来加载

Generate_only,sqlldr不加载任何数据，只是生成所执行的sql ddl和dml语句，并放到它创建的日志文件中

5.1.1 手工创建外部表

1 创建一个目录

conn /as sysdba

create or replace directory xxx as ‘f:\sqlldr\’

grangt read,write on directory xxx to scott

2创建外部表

   CREATE TABLE "SYS_SQLLDR_X_EXT_BULK_NUMBERS"
(
  "ID" NUMBER,
  "A" VARCHAR2(20 CHAR),
  "B" NUMBER,
  "C" VARCHAR(255),
  "DATE1" DATE
)
ORGANIZATION external
(TYPE oracle_loader
  DEFAULT DIRECTORY xxx
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
SKIP 4
FIELDS TERMINATED BY ","
(ID,A,B,C,DATE1)
)
Location(
'DEOMT.CTL'
)

5.1.2 SQLLDR创建

Direct=true会覆盖external_table=generate_only，

:\sqlldr>sqlldr scott/987064@orcl control=1024TEST.CTL external_table=generate_only

会根据控制文件中生成一个log文件

A 首先会创建一个目录

CREATE DIRECTORY statements needed for files

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

CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'F:\sqlldr\'

B 创建外部表

CREATE TABLE statement for external table:

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

CREATE TABLE "SYS_SQLLDR_X_EXT_BULK_NUMBERS"
  (
  "ID" NUMBER,
  "A" VARCHAR2(20 CHAR),
  "B" NUMBER,
  "C" VARCHAR(255),
  "DATE1" DATE
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'1024TEST.bad'
    DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'1024TEST.dsc'
    LOGFILE '1024TEST.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "ID" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "A" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "B" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "C" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "DATE1" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        DATE_FORMAT DATE MASK "MM-DD-YYYY HH24:MI:SS"
    )
  )
  location
  (
    '1024TEST.csv'
  )
)REJECT LIMIT UNLIMITED

1 type：oracle_loader传统方式

Oracle_dump数据泵

2 DEFAULT DIRECTORY 指定数据文件所在路径对于的directory的名称

3 Records 该字句指定记录结束标记 默认为：RECORDS DELIMITED BY NEWLINE

4 Badfile 错误文件名和路径

5 Logfile 日志文件名

6 Readsize oracle读取输入数据文件所用的默认缓冲区，READSIZE 1048576=1m

7 Skip 跳过的记录数

8 FIELDS TERMINATED BY ","

9 REJECT ROWS WITH ALL NULL FIELDS 该字句表示如果要加载的字段的所有行都是空值，则外部表并不执行加载，

10 Location 用来指定来源数据，

11 REJECT LIMIT UNLIMITED 用来接受查询数据时能够接受的错误数，不指定默认是0，UNLIMITED 表示不限制

INSERT statements used to load internal tables:

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

INSERT /*+ append */ INTO BULK_NUMBERS
(
  ID,
  A,
  B,
  C,
  DATE1
)
SELECT
  "ID",
  "A",
  "B",
  0,
  "DATE1"
FROM "SYS_SQLLDR_X_EXT_BULK_NUMBERS"

然后手动执行sql

5.2 指定加载log

alter table xxxx  access parameters
ACCESS PARAMETERS
  (
RECORDS DELIMITED BY NEWLINE
SKIP 5
LOGFILE '1024TEST.log_xt'
FIELDS TERMINATED BY ","
(ID,A,B,C,DATE1)

查看日志或错误日志

create table et_bad
    ( text1 varchar2(4000) ,
      text2 varchar2(4000) ,
      text3 varchar2(4000)  )
   organization external
   (type oracle_loader
    default directory SYS_SQLLDR_XT_TMPDIR_00000
    access parameters   (
   records delimited by newline
     fields
      missing field values are null
     ( text1 position(1:4000),
       text2 position(4001:8000),
       text3 position(8001:12000)  )  )
   location ('demo1.bad')  );

5.3 使用外部表加载不同的文件

alter table xxxx location(‘xxxx.ctl’,’xxxx.dat’)

5.4 多用户问题

alter table xxxx location(‘xxxx1.dat’,’xxxx.dat’)

5.5 外部表加载的效率

主要由三方面 CPU,CACHE,I/O

CPU 对于cpu，只要空闲，oracle就会利用它

I/O 需要dba认真规划，是否启用了归档，并行等，对io影响最常见的调整方式

Paralled 设置并行参数

Access parameters中显示指定nologfile，nobadfile，nodisfile等降低磁盘io

CACHE 中，access parameters中2个参数，bindsize跟date_cache

6 数据泵卸载 10g以后

1 首先创建一个目录

Create or replace directory as xxx ‘f:\mydb\’

create  directory  tmp  as 'f:\mydb\'

2 然后准备一个简单的select语句向这个目录中卸载数据

create table all_objects_unload
    organization external
   ( type oracle_datapump
     default directory xxx
     location( 'allobjects.dat' )   )
   as
   select
   *
from all_objects;
create table all_objects_unload
    organization external
   ( type oracle_datapump
     default directory tmp
     location( 'allobjects.dat' )   )
   as
   select
   *
  from bulk_numbers;

3 把allobjects.dat 改数据文件移植到另外一个服务器，然后在提取此ddl

select dbms_metadata.get_ddl( 'TABLE', 'ALL_OBJECTS_UNLOAD' )  from dual;
select dbms_metadata.get_ddl( 'TABLE', 'ALL_OBJECTS_UNLOAD' )  from dual;

  CREATE TABLE "SCOTT"."ALL_OBJECTS_UNLOAD"
   (    "ID" NUMBER,
    "A" VARCHAR2(20 CHAR),
    "B" NUMBER,
    "C" NUMBER,
    "DATE1" DATE
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "TMP"

      LOCATION
       ( 'allobjects.dat'
       )
    )

然后 insert /*+ append */ into some_table select * from all_objects_unload;

CREATE TABLE xxx
(
  ID     NUMBER,
  A      VARCHAR2(20 CHAR),
  B      NUMBER,
  C      NUMBER,
  DATE1  DATE
);
insert /*+ append */ into xxx select * from all_objects_unload;