SQLLDR Case Study 6: Loading Data Using the Direct Path Load Method

最新推荐文章于 2019-09-29 10:15:17 发布

cnk45946

最新推荐文章于 2019-09-29 10:15:17 发布

阅读量100

点赞数

文章标签：数据库操作系统

一描述

Case Study 6: Loading Data Using the Direct Path Load Method: Loads data using the direct path load method.

This case study loads the emp table using the direct path load method and concurrently builds all indexes. It illustrates the following functions:

Use of the direct path load method to load and index data.
How to specify the indexes for which the data is presorted.
The NULLIF clause.
Loading all-blank numeric fields as NULL.

In this example, field positions and datatypes are specified explicitly.

二操作环境

OS info

windows server 2003 32bit

DB info

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 150
SQL> COL PRODUCT FORMAT A55
SQL> COL VERSION FORMAT A15
SQL> COL STATUS FORMAT A15
SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  9.0.1.1.1       Production
Oracle9i Enterprise Edition                             9.0.1.1.1       Production
PL/SQL                                                  9.0.1.1.1       Production
TNS for 32-bit Windows:                                 9.0.1.1.0       Production

SQL>

other

目录结构:C:\oracle\product\9.2.0\db_1\rdbms\demo

三过程设计

1.准备的源flat数据 ulcase6.dat
7499 ALLEN      SALESMAN 7698 1600.00   300.00 30
7566 JONES      MANAGER   7839 3123.75          20
7654 MARTIN     SALESMAN 7698 1312.50 1400.00 30
7658 CHAN       ANALYST   7566 3450.00          20
7782 CLARK      MANAGER   7839 2572.50          10
7839 KING       PRESIDENT       5500.00          10
7934 MILLER     CLERK     7782   920.00          10

2.相关文件ulcase6.sql
rem
rem $Header: ulcase6.sql 14-jul-99.14:25:48 mjaeger Exp $
rem
rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved.
rem
rem    NAME
rem      ulcase6.sql -
rem    DESCRIPTION
rem
rem    RETURNS
rem
rem    NOTES
rem
rem    MODIFIED   (MM/DD/YY)
rem     mjaeger    07/14/99 - bug 808870: OCCS: convert tabs, no long lines
rem     jstenois   06/17/99 - cleanup tables before load and show feedback
rem     ksudarsh   02/21/94 - create unique index "empix"
rem     ksudarsh   03/11/93 - comment out vms specific host command
rem     ksudarsh   12/29/92 - Creation
rem     ksudarsh   12/28/92 - Don't drop dept
rem     cheigham   08/28/91 - Creation
rem     cheigham   03/19/91 -         Creation

set termout off
rem host write sys$output "Building case 6 demonstration tables. Please wait"

drop table emp;

create table emp
       (empno number(4) not null,
        ename char(10),
        job char(9),
        mgr number(4),
        hiredate date,
        sal number(7,2),
        comm number(7,2),
        deptno number(2));

create unique index empix on emp(empno);

exit

3.相关文件ulcase6.ctl
-- Copyright (c) 1991, 1997 by Oracle Corporation
--   NAME
--     ulcase6.ctl -
--   DESCRIPTION
--
--   RETURNS
--
--   NOTES
--
--   MODIFIED   (MM/DD/YY)
--    msakayed   09/02/97 - remove nullif on empno
--    jhealy     01/27/95 - merge changes from branch 1.2.720.1
--    ksudarsh   11/06/92 - infile is ulcase6
--    cheigham   08/28/91 - Creation
--
-- $Header: ulcase6.ctl 02-sep-97.15:39:53 msakayed Exp $
--
load data
infile 'ulcase6.dat'
replace
into table emp
sorted indexes (empix)
(empno position(1:4),
ename position(6:15),
job position(17:25),
mgr position(27:30) nullif mgr=blanks,
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks)

4.以scott用户连接数据库
5.scott用户运行ulcase6.sql初始化环境
6.在cmd命令下执行sqlldr加载ulcase6.ctl控制文件命令
7.查看sqlldr日志信息
8.查看sqlldr bad文件信息
9.查看数据库加载成功的数据

四详细步骤操作

1.以scott用户连接数据库
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期三 8月 8 00:07:36 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL>

2.scott用户运行ulcase6.sql初始化环境
SQL> @ulcase6
从Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production中断开

C:\oracle\product\9.2.0\db_1\rdbms\demo>

3.在cmd命令下执行sqlldr加载ulcase6.ctl控制文件命令
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlldr "scott/tiger" control=ulcase6.ctl direct=true

SQL*Loader: Release 9.0.1.1.1 - Production on 星期三 8月 8 00:09:59 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

加载完成 - 逻辑记录计数7

C:\oracle\product\9.2.0\db_1\rdbms\demo>

4.查看sqlldr日志信息

SQL*Loader: Release 9.0.1.1.1 - Production on 星期三 8月 8 00:09:59 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

控制文件: ulcase6.ctl
数据文件: ulcase6.dat
错误文件: ulcase6.bad
废弃文件: 未作指定
:
(可废弃所有记录)

加载数: ALL
跳过数: 0
允许的错误: 50
继续:    未作指定
所用路径:       直接

表EMP
已加载从每个逻辑记录
插入选项对此表REPLACE生效

   列名                        位置      长度中止包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO                                1：4     4           CHARACTER
ENAME                               6：15    10           CHARACTER
JOB                                17：25     9           CHARACTER
MGR                                27：30     4           CHARACTER
    NULL if MGR =BLANKS
SAL                                32：39     8           CHARACTER
    NULL if SAL =BLANKS
COMM                               41：48     8           CHARACTER
    NULL if COMM =BLANKS
DEPTNO                             50：51     2           CHARACTER
    NULL if EMPNO =BLANKS

表 EMP 的以下索引已处理:
索引 SCOTT.EMPIX 已成功载入，具有 7 个关键字

表EMP:
7 行加载成功
由于数据错误, 0 行没有加载。
由于所有 WHEN 子句失败, 0 行没有加载。
由于所有字段都为空的, 0 行没有加载。

在直接路径中没有使用结合数组大小
列数组行数:    5000
流缓冲区字节数: 256000
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数:        0
读取的逻辑记录总数:        7
拒绝的逻辑记录总数:        0
废弃的逻辑记录总数:        0
由 SQL* 加载程序主线程加载的流缓冲区总数:        2
由 SQL* 加载程序加载线程加载的流缓冲区总数:        0

从星期三 8月 08 00:09:59 2012开始运行
在星期三 8月 08 00:10:00 2012处运行结束

经过时间为: 00: 00: 01.64
CPU 时间为: 00: 00: 00.05(可?

5.查看sqlldr bad文件信息
(注:无)
6.查看数据库加载成功的数据
C:\oracle\product\9.2.0\db_1\rdbms\demo>sqlplus "scott/tiger"

SQL*Plus: Release 9.0.1.0.1 - Production on 星期三 8月 8 00:11:35 2012

(c) Copyright 2001 Oracle Corporation. All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> set lines 120
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698                  1600        300         30
      7566 JONES      MANAGER         7839               3123.75                    20
      7654 MARTIN     SALESMAN        7698                1312.5       1400         30
      7658 CHAN       ANALYST         7566                  3450                    20
      7782 CLARK      MANAGER         7839                2572.5                    10
      7839 KING       PRESIDENT                             5500                    10
      7934 MILLER     CLERK           7782                   920                    10

已选择7行。

SQL>