sqlldr用法

SQL*Loader Features

1.从不同的系统上load数据文件

2.在同一个会话中加载多个文件

3.在同一个会话中将一个数据文件载入多个表

4.指定数据的字符集

5.有选择地加载数据

6.在载入数据文件的时候使用函数

7.在指定的列上设置序列

8.使用操作系统的文件系统访问数据文件

9.从磁盘、磁带或命名管道中加载数据

10.生成复杂的错误报告

11.加载复杂的关系数据

12.使用次要文件加载lob和集合

13.提供了优越的性能

1.最基本的

SQL> create table wahaha3(a integer,b integer);

Table created.

SQL> create table wahaha4 as select * from wahaha3;

Table created.

[oracle@wahaha3 test]$ more a.txt

1,2

3,4

5,6

7,8

9,10

[oracle@wahaha3 test]$ more a.ctl

LOAD DATA

append INTO TABLE wahaha4

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger control=a.ctl data=a.txt log=a.log

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 04:39:48 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

SQL> conn scott/tiger

Connected.

SQL> select * from wahaha4;

         A          B

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

         1          2

         3          4

         5          6

         7          8

         9         10

2.从不同的系统上load数据文件

[oracle@wahaha3 test]$ sqlplus scott/tiger

SQL> truncate table wahaha4;

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl data=a.txt log=a.log

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 05:42:43 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5        

3.在同一个会话中加载多个文件

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

infile 'a.txt'

infile 'b.txt'

truncate INTO TABLE wahaha4

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 05:53:25 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-284: Warning: Input data file b.txt specified multiple times.

Commit point reached - logical record count 5

Commit point reached - logical record count 10

[oracle@wahaha3 test]$ sqlplus scott/tiger

SQL> select * from wahaha4;

         A          B

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

         1          2

         3          4

         5          6

         7          8

         9         10

        11         12

        13         14

        15         16

        17         18

        19         20

10 rows selected. 

4.在同一个会话中将一个数据文件载入多个表

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

infile 'a.txt'

truncate INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

INTO TABLE wahaha4

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a position(1),

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 07:12:08 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

SQL> select * from wahaha4;

         A          B

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

         1          2

         3          4

         5          6

         7          8

         9         10

SQL> select * from wahaha3;

         A          B

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

         1          2

         3          4

         5          6

         7          8

         9         10

5.指定数据的字符集

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

CHARACTERSET UTF8

infile 'a.txt'

truncate INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 10:47:27 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

6.有选择地加载数据

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

CHARACTERSET UTF8

infile 'a.txt'

truncate INTO TABLE wahaha3

when a!='1'

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 10:55:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

SQL> select * from wahaha3;

         A          B

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

         3          4

         5          6

         7          8

         9         10

7.在载入数据文件的时候使用函数

SQL> create or replace function get2(id integer ) return integer as

  2  begin

  3  return id*2;

  4  end;

  5  /

Function created.

[oracle@wahaha3 test]$ cat a.ctl

when a!='1'

LOAD DATA

CHARACTERSET UTF8

infile 'a.txt'

truncate INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a "get2(:a)",

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 11:30:54 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

SQL> select * from wahaha3;

         A          B

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

         2          2

         6          4

        10          6

        14          8

        18         10

8.在指定的列上设置序列

SQL> create sequence seq_sqlldr start with 1;

Sequence created.

SQL> select seq_sqlldr.nextval from dual;

   NEXTVAL

----------

         1

SQL> /

   NEXTVAL

----------

         2    

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

CHARACTERSET UTF8

infile 'a.txt'

truncate INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  ss filler,

  b,

  a "seq_sqlldr.nextval"

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 12:33:45 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

SQL> select * from wahaha3;

         A          B

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

        28          2

        29          4

        30          6

        31          8

        32         10

9.使用操作系统的文件系统访问数据文件

传统的load其实是执行标准的insert语句，这个directory path loader(direct=true)是直接进了数据文件，并且穿件了需要的块

如果想使用这个功能，那么必须执行$ORACLE_HOME/rdbms/admin/catldr.sql 这个脚本

对于传统的load，禁用索引和约束可以大大提高性能，当使用directory loader执行并行的时候 可以通过设置skip_index_maintenance这个参数可以绕过索引的维护

skip_index_maintenance=true 意味着在并行的时候不需要重建索引

SQL> alter table wahaha3 add constraint PK_a primary key(a);

Table altered.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_A                           WAHAHA3                        VALID

SQL> select constraint_name,table_name,status from user_constraints where table_name='WAHAHA3';

CONSTRAINT_NAME                TABLE_NAME                     STATUS

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

PK_A                           WAHAHA3                        ENABLE

SQL> conn / as sysdba

Connected.

SQL>  @ ?/rdbms/admin/catldr.sql

[oracle@wahaha3 test]$ vi a.ctl

  ss filler,

  a "seq_sqlldr.nextval"

LOAD DATA

CHARACTERSET UTF8

infile 'a.txt'

truncate INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad direct=true;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 13:30:29 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 5.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_A                           WAHAHA3                        VALID

SQL> select constraint_name,table_name,status from user_constraints where table_name='WAHAHA3';

CONSTRAINT_NAME                TABLE_NAME                     STATUS

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

PK_A                           WAHAHA3                        ENABLED

--这里没有失效

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad direct=true parallel=true;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 13:38:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-279: Only APPEND mode allowed when parallel load specified.

--修改a.ctl

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

infile 'a.txt'

append INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad direct=true parallel=true;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 13:39:11 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-951: Error calling once/load initialization

ORA-26002: Table SCOTT.WAHAHA3 has index defined upon it.

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad direct=true parallel=true skip_index_maintenance=true;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 13:40:05 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 5.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_A                           WAHAHA3                        UNUSABLE

SQL> select constraint_name,table_name,status,validated from user_constraints where table_name='WAHAHA3'

CONSTRAINT_NAME                TABLE_NAME STATUS   VALIDATED

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

PK_A                           WAHAHA3    ENABLED  VALIDATED

--所以这里应该是direct=true parallel=true都打开的时候，会校验索引，通过设置skip_index_maintenance=true

跳过后，执行导入，这个时候约束没有影响，但是索引失效了

SQL> alter index pk_a rebuild;

Index altered.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

INDEX_NAME                     TABLE_NAME STATUS

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

PK_A                           WAHAHA3    VALID

--这里如果我们继续导入

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad direct=true parallel=true skip_index_maintenance=true;

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 13:47:46 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Load completed - logical record count 5.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

INDEX_NAME                     TABLE_NAME                     STATUS

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

PK_A                           WAHAHA3                        UNUSABLE

SQL> select constraint_name,table_name,status,validated from user_constraints where table_name='WAHAHA3';

CONSTRAINT_NAME                TABLE_NAME STATUS   VALIDATED

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

PK_A                           WAHAHA3    ENABLED  VALIDATED

SQL> select * from wahaha3;

         A          B

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

         1          2

         3          4

         5          6

         7          8

         9         10

         1          2

         3          4

         5          6

         7          8

         9         10

--这里a存在主键，但是却导入进去了，并且逐渐的enable和valid都正常，这个时候我们继续rebuild索引

SQL> alter index pk_a rebuild;

alter index pk_a rebuild

*

ERROR at line 1:

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

--重建不了了

--插入一条记录测试一下

SQL> insert into wahaha3(a,b)values(1,2);

insert into wahaha3(a,b)values(1,2)

*

ERROR at line 1:

ORA-01502: index 'SCOTT.PK_A' or partition of such index is in unusable state

--进不去

SQL> insert into wahaha3(a,b)values(111,111);

insert into wahaha3(a,b)values(111,111)

*

ERROR at line 1:

ORA-01502: index 'SCOTT.PK_A' or partition of such index is in unusable state

--还是进不去

--这个时候相当于这个表已经有问题了，索引重建不了，约束状态正常

--这里相当于表中存在重复数据，但主键没有失效，很矛盾啊，我们对约束进行处理一下

SQL> alter table wahaha3 enable novalidate constraint pk_a;

Table altered.

SQL> select constraint_name,table_name,status,validated from user_constraints where table_name='WAHAHA3';

CONSTRAINT_NAME                TABLE_NAME STATUS   VALIDATED

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

PK_A                           WAHAHA3    ENABLED  NOT VALIDATED

SQL> alter index pk_a rebuild;

alter index pk_a rebuild

*

ERROR at line 1:

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

SQL> alter table wahaha3 disable validate constraint pk_a;

alter table wahaha3 disable validate constraint pk_a

*

ERROR at line 1:

ORA-14063: Unusable index exists on unique/primary constraint key

SQL> alter table wahaha3 disable novalidate constraint pk_a;

Table altered.

SQL> alter index pk_a rebuild;

alter index pk_a rebuild

*

ERROR at line 1:

ORA-01418: specified index does not exist

SQL> 

当disable的时候，索引应该就删除了，但是为什么novalidate后还是不可以进行索引的rebuild

--继续测试一下

SQL> truncate table wahaha3;

Table truncated.

SQL> alter table wahaha3 drop constraint pk_a;

Table altered.

SQL> alter table wahaha3 add constraint pk_a primary key(a) enable novalidate;

Table altered.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

INDEX_NAME                     TABLE_NAME STATUS

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

PK_A                           WAHAHA3    VALID

SQL> alter table wahaha3 drop constraint pk_a;

Table altered.

SQL> alter table wahaha3 add constraint pk_a primary key(a) disable validate;

Table altered.

SQL> select index_name,table_name,status  from user_indexes where table_name='WAHAHA3';

no rows selected

SQL> 

--这里说明索引与enable有关，与valide没有关系，和刚才的很矛盾啊，刚才设置了novalid后索引还是无法重建

10.从磁盘、磁带或命名管道中加载数据

参考：https://dylanwan.wordpress.com/2008/06/24/use-sqlloader-with-named-pipe/

[oracle@wahaha3 test]$ mkfifo mypipe.dat

[oracle@wahaha3 test]$ ls -ltr

prw-r--r--. 1 oracle oinstall    0 Jul 24 17:10 mypipe.dat

[oracle@wahaha3 test]$ cat a.txt > mypipe.dat &

[1] 5345

[oracle@wahaha3 test]$ ls -ltr

prw-r--r--. 1 oracle oinstall    0 Jul 24 17:10 mypipe.dat

[oracle@wahaha3 test]$ cat a.ctl

LOAD DATA

infile 'mypipe.dat'

append INTO TABLE wahaha3

Fields terminated by "," Optionally enclosed by '"'

TRAILING NULLCOLS

(

  a,

  b

)

[oracle@wahaha3 test]$ sqlldr userid=scott/tiger@wahaha3 control=a.ctl log=a.log bad=a.bad;                                                      

SQL*Loader: Release 11.2.0.3.0 - Production on Sun Jul 24 17:17:48 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 5

[1]+  Done                    cat a.txt > mypipe.dat

SQL> select * from wahaha3;

         A          B

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

         1          2

         3          4

         5          6

         7          8

         9         10

11.生成复杂的错误报告

就那个log

12.加载复杂的关系数据

这块比较复杂，比较多，这里引用官方文档中的一个例子

LOAD DATA

   INFILE *

   REPLACE

   INTO TABLE personnel

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

      (deptno        INTEGER EXTERNAL(3),

       deptname      CHAR,

       employee      COLUMN OBJECT

         (name       CHAR,

          ssn        INTEGER EXTERNAL(9),

          empid      INTEGER EXTERNAL(5)))

BEGINDATA

1  101,Mathematics,Johny Q.,301189453,10249,

   237,Physics,"Albert Einstein",128606590,10030,   

13.使用次要文件加载lob和集合，这里引用官方文档中的一个例子

 LOAD DATA

   INFILE 'sample.dat' "str '\n' "

   INTO TABLE dept

   REPLACE

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

   (

     dept_no       CHAR(3),

     dname         CHAR(25) NULLIF dname=BLANKS,

1    emps          VARRAY TERMINATED BY ':'

     (

       emps        COLUMN OBJECT

       (

         name      CHAR(30),

         age       INTEGER EXTERNAL(3),

2        emp_id    CHAR(7) NULLIF emps.emps.emp_id=BLANKS

     )

   ),

3   proj_cnt      FILLER CHAR(3),

4   projects      NESTED TABLE SDF (CONSTANT "pr.txt" "fix 57") COUNT (proj_cnt)

  (

    projects    COLUMN OBJECT

    (

      project_id        POSITION (1:5) INTEGER EXTERNAL(5),

      project_name      POSITION (7:30) CHAR 

                        NULLIF projects.projects.project_name = BLANKS

    )

  )

)

Datafile (sample.dat)

 101,MATH,"Napier",28,2828,"Euclid", 123,9999:0

 210,"Topological Transforms",:2

Secondary Datafile (SDF) (pr.txt)

21034 Topological Transforms

77777 Impossible Proof