sql loader的应用（二）

 

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@solaris10 sqlldr]$sqlldr scott/tiger control=emp.ctl log=emp.log

——执行导入的语句，指明用户/口令、控制文件的位置和导入生成日志的位置

SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:30:47 2012

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

Commit point reached - logical record count 14

——查看日志

[oracle@work sqlldr]$ more emp.log

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

SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:30:47 2012

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

Commit point reached - logical record count 14

[oracle@solaris10 sqlldr]$more emp.log

SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:30:47 2012

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

Control File:   emp.ctl

Data File:      /export/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 Fri Mar 16 14:30:47 2012

Run ended on Fri Mar 16 14:30:47 2012

Elapsed time was:     00:00:00.50

CPU time was:         00:00:00.02

——验证

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.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';

TABLE_NAME             NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                         14         60           68

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

[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,1980-12-17 00:00:00,800,,20

7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30

7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30

7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20

7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30

7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30

7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10

7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20

7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10

7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30

7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20

7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30

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

SQL*Loader: Release 10.2.0.2.0 - Production on Fri Mar 16 14:46:04 2012

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

Commit point reached - logical record count 14

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.

SQL> analyze table emp1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';

TABLE_NAME             NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                         28         60           68——普通导入是将数据导入到高水位上的块

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.

SQL> analyze table emp1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='EMP1';

TABLE_NAME             NUM_ROWS     BLOCKS EMPTY_BLOCKS

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

EMP1                         29         65           6

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