oracle dba笔记,Oracle DBA课程系列笔记（20）

第二十章：  数据装载 SQL Loader

1、sql loader ：将外部数据(比如文本型)数据导入oracle database。(用于数据导入、不同类型数据库数据迁移)

2、sqlloader 导入数据原理：在段(segment 表)insert 记录

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

2)direct： 将记录插入到segment的HWM(高水位线)以上的从未使用过的块，加快插入速度

3)sql loader 帮助

C:\Documents and Settings\liqx>sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 8月 11 13:57:10 2011

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

用法: SQLLDR keyword=value [,keyword=value,...]

有效的关键字:

userid -- ORACLE 用户名/口令

control -- 控制文件名

log -- 日志文件名

bad -- 错误文件名

data -- 数据文件名

discard -- 废弃文件名

discardmax -- 允许废弃的文件的数目         (全部默认)

skip -- 要跳过的逻辑记录的数目  (默认 0)

load -- 要加载的逻辑记录的数目  (全部默认)

errors -- 允许的错误的数目         (默认 50)

rows -- 常规路径绑定数组中或直接路径保存数据间的行数

(默认: 常规路径 64, 所有直接路径)

bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)

silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)

direct -- 使用直接路径                     (默认 FALSE)

parfile -- 参数文件: 包含参数说明的文件的名称

parallel -- 执行并行加载                    (默认 FALSE)

file -- 要从以下对象中分配区的文件

skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)

skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)

commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)

readsize -- 读取缓冲区的大小               (默认 1048576)

external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NO

T_USED)

columnarrayrows -- 直接路径列数组的行数  (默认 5000)

streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)

multithreading -- 在直接路径中使用多线程

resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)

resumable_name -- 有助于标识可恢复语句的文本字符串

resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)

date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)

PLEASE NOTE: 命令行参数可以由位置或关键字指定

。前者的例子是 'sqlload

scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo

userid=scott/tiger'.位置指定参数的时间必须早于

但不可迟于由关键字指定的参数。例如,

允许 'sqlldr scott/tiger control=foo logfile=log', 但是

不允许 'sqlldr scott/tiger control=foo log', 即使

参数 'log' 的位置正确。

C:\Documents and Settings\liqx>

4、sqlldr  应用

1)模拟生成数据源

---------    建立脚本emp.sql

[oracle@work sqlldr]$ more emp.sql

set heading off

set feedback off

set time off

set linesize 120

set pagesize 0

set echo off

set trimspool off

spool /home/oracle/sqlldr/emp.dat

select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from scott.emp;

spool off;

SQL> @/home/oracle/sqlplus/emp.sql

SP2-0310: unable to open file "/home/oracle/sqlplus/emp.sql"

SQL> @/home/oracle/sqlldr/emp.sql

SQL> set heading off

SQL> set feedback off

SQL> set time off

SQL> set linesize 120

SQL> set pagesize 0

SQL> set echo off

7369,SMITH,CLERK,7902,17-DEC-80,8000,,20

7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

7566,JONES,MANAGER,7839,02-APR-81,8000,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30

7782,CLARK,MANAGER,7839,09-JUN-81,2000,,10

7788,SCOTT,ANALYST,7566,19-APR-87,2000,100,10

7839,KING,PRESIDENT,,17-NOV-81,2000,,10

7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

7876,ADAMS,CLERK,7788,23-MAY-87,8000,,20

7900,JAMES,CLERK,7698,03-DEC-81,950,,30

7902,FORD,ANALYST,7566,03-DEC-81,8000,,20

7934,MILLER,CLERK,7782,23-JAN-82,2000,,10

--------查看数据源

[oracle@work sqlldr]$ more emp.dat

7369,SMITH,CLERK,7902,17-DEC-80,8000,,20

7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

7566,JONES,MANAGER,7839,02-APR-81,8000,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30

7782,CLARK,MANAGER,7839,09-JUN-81,2000,,10

7788,SCOTT,ANALYST,7566,19-APR-87,2000,100,10

7839,KING,PRESIDENT,,17-NOV-81,2000,,10

7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

7876,ADAMS,CLERK,7788,23-MAY-87,8000,,20

7900,JAMES,CLERK,7698,03-DEC-81,950,,30

7902,FORD,ANALYST,7566,03-DEC-81,8000,,20

7934,MILLER,CLERK,7782,23-JAN-82,2000,,10

2)导入表emp1

12:12:20 SQL> analyze table emp1 compute statistics;

Table analyzed.

12:12:50 SQL> select table_name,num_rows,blocks,empty_blockS from user_tables

12:12:56   2   where table_name='EMP1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                                    0          4            4

12:12:57 SQL>

3)普通模式导入

建立控制文件

[oracle@work sqlldr]$ vi emp.ctl

load data

infile '/home/oracle/sqlldr/emp.dat'

insert              //insert 插入表必须是空表，非空表用append

into table emp1

fields terminated  by   ','

optionally enclosed by '"'

(

empno,

ename,

job,

mgr,

hiredate,

comm,

sal,

deptno)

执行导入(normal)

[oracle@work sqlldr]$ sqlldr scott/tiger control=emp.ctl log=emp.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 11 12:18:36 2011

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

Commit point reached - logical record count 14

[oracle@work sqlldr]$ more emp.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 11 12:18:36 2011

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

Control File:   emp.ctl

Data File:      /home/oracle/sqlldr/emp.dat

Bad File:     emp.bad

Discard File:  none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 256000 bytes

Continuation:    none specified

Path used:      Conventional

Table EMP1, loaded from every logical record.

Insert option in effect for this table: INSERT

Column Name                  Position   Len  Term Encl Datatype

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

EMPNO                               FIRST     *   ,  O(") CHARACTER

ENAME                                NEXT     *   ,  O(") CHARACTER

JOB                                  NEXT     *   ,  O(") CHARACTER

MGR                                  NEXT     *   ,  O(") CHARACTER

HIREDATE                             NEXT     *   ,  O(") CHARACTER

COMM                                 NEXT     *   ,  O(") CHARACTER

SAL                                  NEXT     *   ,  O(") CHARACTER

DEPTNO                               NEXT     *   ,  O(") CHARACTER

Table EMP1:

14 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array:                 132096 bytes(64 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:            14

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Thu Aug 11 12:18:36 2011

Run ended on Thu Aug 11 12:18:37 2011

Elapsed time was:     00:00:00.59

CPU time was:         00:00:00.09

[oracle@work sqlldr]$ exit

exit

验证：

12:18:51 SQL> select * from scott.emp1;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17-DEC-80                  8000         20

7499 ALLEN      SALESMAN        7698 20-FEB-81        300       1600         30

7521 WARD       SALESMAN        7698 22-FEB-81        500       1250         30

7566 JONES      MANAGER         7839 02-APR-81                  8000         20

7654 MARTIN     SALESMAN        7698 28-SEP-81       1400       1250         30

7698 BLAKE      MANAGER         7839 01-MAY-81                  2850         30

7782 CLARK      MANAGER         7839 09-JUN-81                  2000         10

7788 SCOTT      ANALYST         7566 19-APR-87        100       2000         10

7839 KING       PRESIDENT            17-NOV-81                  2000         10

7844 TURNER     SALESMAN        7698 08-SEP-81          0       1500         30

7876 ADAMS      CLERK           7788 23-MAY-87                  8000         20

7900 JAMES      CLERK           7698 03-DEC-81                   950         30

7902 FORD       ANALYST         7566 03-DEC-81                  8000         20

7934 MILLER     CLERK           7782 23-JAN-82                  2000         10

14 rows selected.

12:18:57 SQL>

12:19:45 SQL> analyze table emp1 compute statistics;

Table analyzed.

12:19:54 SQL> select table_name,num_rows,blocks,empty_blockS from user_tables

12:20:04   2    where table_name='EMP1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                                   14          4            4

12:20:12 SQL>

-----数据源和控制文件在一起

[oracle@work sqlldr]$ vi emp.ctl

load data

infile *

append

into table emp1

fields terminated  by   ','

optionally enclosed by '"'

(

empno,

ename,

job,

mgr,

hiredate,

comm,

sal,

deptno)

begindata

7369,SMITH,CLERK,7902,17-DEC-80,8000,,20

7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30

7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30

7566,JONES,MANAGER,7839,02-APR-81,8000,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30

7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30

7782,CLARK,MANAGER,7839,09-JUN-81,2000,,10

7788,SCOTT,ANALYST,7566,19-APR-87,2000,100,10

7839,KING,PRESIDENT,,17-NOV-81,2000,,10

7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

7876,ADAMS,CLERK,7788,23-MAY-87,8000,,20

7900,JAMES,CLERK,7698,03-DEC-81,950,,30

7902,FORD,ANALYST,7566,03-DEC-81,8000,,20

7934,MILLER,CLERK,7782,23-JAN-82,2000,,10

[oracle@work sqlldr]$ sqlldr scott/tiger control=emp.ctl log=emp.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 11 12:23:32 2011

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

Commit point reached - logical record count 14

[oracle@work sqlldr]$ more emp.log

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 11 12:23:32 2011

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

Control File:   emp.ctl

Data File:      emp.ctl

Bad File:     emp.bad

Discard File:  none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 256000 bytes

Continuation:    none specified

Path used:      Conventional

Table EMP1, loaded from every logical record.

Insert option in effect for this table: APPEND

Column Name                  Position   Len  Term Encl Datatype

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

EMPNO                               FIRST     *   ,  O(") CHARACTER

ENAME                                NEXT     *   ,  O(") CHARACTER

JOB                                  NEXT     *   ,  O(") CHARACTER

MGR                                  NEXT     *   ,  O(") CHARACTER

HIREDATE                             NEXT     *   ,  O(") CHARACTER

COMM                                 NEXT     *   ,  O(") CHARACTER

SAL                                  NEXT     *   ,  O(") CHARACTER

DEPTNO                               NEXT     *   ,  O(") CHARACTER

Table EMP1:

14 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array:                 132096 bytes(64 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:            14

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Thu Aug 11 12:23:32 2011

Run ended on Thu Aug 11 12:23:32 2011

Elapsed time was:     00:00:00.23

CPU time was:         00:00:00.07

[oracle@work sqlldr]$ !exit

exit

exit

12:23:43 SQL> select * from scott.emp1;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17-DEC-80                  8000         20

7499 ALLEN      SALESMAN        7698 20-FEB-81        300       1600         30

7521 WARD       SALESMAN        7698 22-FEB-81        500       1250         30

7566 JONES      MANAGER         7839 02-APR-81                  8000         20

7654 MARTIN     SALESMAN        7698 28-SEP-81       1400       1250         30

7698 BLAKE      MANAGER         7839 01-MAY-81                  2850         30

7782 CLARK      MANAGER         7839 09-JUN-81                  2000         10

7788 SCOTT      ANALYST         7566 19-APR-87        100       2000         10

7839 KING       PRESIDENT            17-NOV-81                  2000         10

7844 TURNER     SALESMAN        7698 08-SEP-81          0       1500         30

7876 ADAMS      CLERK           7788 23-MAY-87                  8000         20

7900 JAMES      CLERK           7698 03-DEC-81                   950         30

7902 FORD       ANALYST         7566 03-DEC-81                  8000         20

7934 MILLER     CLERK           7782 23-JAN-82                  2000         10

7369 SMITH      CLERK           7902 17-DEC-80                  8000         20

7499 ALLEN      SALESMAN        7698 20-FEB-81        300       1600         30

7521 WARD       SALESMAN        7698 22-FEB-81        500       1250         30

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7566 JONES      MANAGER         7839 02-APR-81                  8000         20

7654 MARTIN     SALESMAN        7698 28-SEP-81       1400       1250         30

7698 BLAKE      MANAGER         7839 01-MAY-81                  2850         30

7782 CLARK      MANAGER         7839 09-JUN-81                  2000         10

7788 SCOTT      ANALYST         7566 19-APR-87        100       2000         10

7839 KING       PRESIDENT            17-NOV-81                  2000         10

7844 TURNER     SALESMAN        7698 08-SEP-81          0       1500         30

7876 ADAMS      CLERK           7788 23-MAY-87                  8000         20

7900 JAMES      CLERK           7698 03-DEC-81                   950         30

7902 FORD       ANALYST         7566 03-DEC-81                  8000         20

7934 MILLER     CLERK           7782 23-JAN-82                  2000         10

28 rows selected.

12:23:47 SQL> analyze table emp1 compute statistics;

Table analyzed.

12:23:51 SQL> select table_name,num_rows,blocks,empty_blockS from user_tables

12:23:52   2    where table_name='EMP1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                                   28          4            4

12:23:54 SQL>

~4) 直接导入(direct)

12:28:23 SQL> analyze table emp1 compute statistics;

Table analyzed.

12:28:26 SQL> select table_name,num_rows,blocks,empty_blockS from user_tables

12:28:27   2    where table_name='EMP1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                                  268          8            0

[oracle@work sqlldr]$ vi emp.ctl

load data

infile *

append

into table emp1

fields terminated  by   ','

optionally enclosed by '"'

(

empno,

ename,

job,

mgr,

hiredate,

comm,

sal,

deptno)           //字段的类型和记录的类型和顺序必须一致。

begindata

7369,SMITH,CLERK,7902,17-DEC-80,8000,,20

[oracle@work sqlldr]$ sqlldr scott/tiger control=emp.ctl log=emp.log direct=y

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 11 12:28:55 2011

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

Load completed - logical record count 1.

[oracle@work sqlldr]$ !exi

exit

exit

12:28:58 SQL> analyze table emp1 compute statistics;

Table analyzed.

12:29:01 SQL> select table_name,num_rows,blocks,empty_blockS from user_tables

12:29:02   2    where table_name='EMP1';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                                  269          9            7

12:29:03 SQL>

---虽然只导入了一条记录，但oracle又重新给我们分配了一个新的extent(8 个blocks)