OCM_Session4_3_Oracle_Loader External Tables

 

• 3. Oracle_Loader External Tables
•  3.1 In the scripts directory, you will find prod_master.dat and prod_master.ctl. Using the information found in these files, create an external table names PROD_MASTER in the SH schema of the PROD database.

在脚本目录里，你会找到 prod_master.dat脚本和 prod_master.ctl脚本，使用在这些文件里找到的信息，创建一个外部表，名字叫 PROD_MASTER，在PROD数据库的SH方案里

• 1.首先找到这两个脚本，查看一下内容；

[oracle@ocm1 ~]$ cd /home/oracle/script/

[oracle@ocm1 script]$ ll

total 12

-rw-r--r-- 1 oracle oinstall  64 Mar 19 14:22 create_bishhr.sql

-rw-r--r-- 1 root   root     154 Mar 27 11:01 prod_master.ctl

-rw-r--r-- 1 root   root      56 Mar 27 11:01 prod_master.dat

[oracle@ocm1 script]$   cat prod_master.ctl 

load data

infile '/home/oracle/script/prod_master.dat'

into table   sh.exm1  fields terminated by whitespace  --插入表 sh.exm1， 以空白符为间隔

TRAILING NULLCOLS

(emp_no,dept_no,name,num)

[oracle@ocm1 script]$   cat prod_master.dat 

1 1 tom 1

2 2 rose 2

3 1 jone 2

4 3 jack 3

5 2 jacky 4

[oracle@ocm1 script]$ 

参考联机文档：

Utilities ==> 13 The ORACLE_LOADER Access Driver

http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_params.htm#i1012274

• 2.使用sqlldr工具生成创建外部表的语句。

[oracle@ocm1 script]$  sqlldr

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 27 11:23:24 2014

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

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

      userid -- ORACLE username/password           

    control -- control file name                  

       log -- log file name                      

       bad -- bad file name                      

      data -- data file name                     

   discard -- discard file name                  

discardmax -- number of discards to allow          (Default all)

      skip -- number of logical records to skip    (Default 0)

      load -- number of logical records to load    (Default all)

    errors -- number of errors to allow            (Default 50)

      rows -- number of rows in conventional path bind array or between direct path data saves

               (Default: Conventional path 64, Direct path all)

  bindsize -- size of conventional path bind array in bytes  (Default 256000)

    silent -- suppress messages during run (header,feedback,errors,discards,partitions)