【实验】【SQL*Loader】使用SQLLDR将Linux的用户文件passwd导入数据库

通过这个实验，我以Linux系统用户文件passwd导入数据库为例，全程记录一下使用SQL*Loader迁移数据的过程，然后小结一下SQLLOADER的优缺点。

1.看一下当前我的系统里passwd文件内容。这个文件中每条记录都是以冒号“:”分割的信息（这个好像地球人都知道，飘过~~）
简单以oracle用户这一行信息为例，解释一下每一个被分割的字段的含义：
条目：oracle:x   :500:500:        :/home/oracle:/bin/bash
解释：用户名 :密码: uid:gid:用户描述  :主目录       :登陆的shell

ora10g@testdb183 /home/oracle$ cat /etc/hosts
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
pcap:x:77:77::/var/arpwatch:/sbin/nologin
mailnull:x:47:47::/var/spool/mqueue:/sbin/nologin
smmsp:x:51:51::/var/spool/mqueue:/sbin/nologin
rpc:x:32:32:Portmapper RPC user:/:/sbin/nologin
rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
nfsnobody:x:4294967294:4294967294:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
avahi-autoipd:x:100:101:avahi-autoipd:/var/lib/avahi-autoipd:/sbin/nologin
avahi:x:70:70:Avahi daemon:/:/sbin/nologin
apache:x:48:48:Apache:/var/www:/sbin/nologin
distcache:x:94:94:Distcache:/:/sbin/nologin
squid:x:23:23::/var/spool/squid:/sbin/nologin
webalizer:x:67:67:Webalizer:/var/www/usage:/sbin/nologin
ntp:x:38:38::/etc/ntp:/sbin/nologin
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
named:x:25:25:Named:/var/named:/sbin/nologin
xfs:x:43:43:X Font Server:/etc/X11/fs:/sbin/nologin
gdm:x:42:42::/var/gdm:/sbin/nologin
sabayon:x:86:86:Sabayon user:/home/sabayon:/sbin/nologin
dovecot:x:97:97:dovecot:/usr/libexec/dovecot:/sbin/nologin
amanda:x:33:6:Amanda user:/var/lib/amanda:/bin/bash
exim:x:93:93::/var/spool/exim:/sbin/nologin
mailman:x:41:41:GNU Mailing List Manager:/usr/lib/mailman:/sbin/nologin
ident:x:98:98::/home/ident:/sbin/nologin
pvm:x:24:24::/usr/share/pvm3:/bin/bash
quagga:x:92:92:Quagga routing suite:/var/run/quagga:/sbin/nologin
privoxy:x:73:73::/etc/privoxy:/sbin/nologin
radvd:x:75:75:radvd user:/:/sbin/nologin
uuidd:x:101:104:UUID generator helper daemon:/var/lib/libuuid:/sbin/nologin
cyrus:x:76:12:Cyrus IMAP Server:/var/lib/imap:/bin/bash
ldap:x:55:55:LDAP User:/var/lib/ldap:/bin/false
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
radiusd:x:95:95:radiusd user:/home/radiusd:/sbin/nologin
pegasus:x:66:65:tog-pegasus OpenPegasus WBEM/CIM services:/var/lib/Pegasus:/sbin/nologin
tomcat:x:91:91:Tomcat:/usr/share/tomcat5:/bin/sh
oracle:x:500:500::/home/oracle:/bin/bash

2.针对passwd文件格式在数据库中创建供导入使用的数据表linux_passwd
sec@ORA10G> create table linux_passwd
  2  ( p_user_name   varchar2(20) constraint pk_linux_passwd primary key,
  3    p_password    varchar2(20),
  4    p_uid         number(20),
  5    p_gid         number(20),
  6    p_description varchar2(100),
  7    p_main_dir    varchar2(100),
  8    p_shell       varchar2(50)
  9  )
 10  /

Table created.

3.准备SQL*Loader的控制文件load_passwd.ctl
注意：注释信息是为了描述方便添加的，在您演示这个实验的时候，记得将这些内容删除掉。
ora10g@testdb183 /home/oracle$ cat load_passwd.ctl
LOAD DATA                 -- 解释：告之SQL*Loader我们要做什么，这里表示要加载数据
INFILE *                  -- 解释：要输入的数据文件，这里“*”表示待加载的数据包含在控制文件中
INTO TABLE linux_passwd   -- 解释：向表linux_passwd中加载数据
REPLACE                   -- 解释：这里可以是insert（要求表为空）、append（在表中追加数据）、replace（delete方式删除原有的记录后再加载数据）和truncate（功能同replace，不过这里会先truncate表，效率更高）
FIELDS TERMINATED BY ':'  -- 解释：分隔符定义为“:”
( p_user_name   ,         -- 解释：以下定义对应于待导入表的结构，输入流数据类型默认为CHAR(255)
  p_password    ,
  p_uid         ,
  p_gid         ,
  p_description ,
  p_main_dir    ,
  p_shell
)
BEGINDATA                 -- 解释：通知SQL*Loader输入数据的描述已经完成，下面内容是待加载的数据
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
pcap:x:77:77::/var/arpwatch:/sbin/nologin
mailnull:x:47:47::/var/spool/mqueue:/sbin/nologin
smmsp:x:51:51::/var/spool/mqueue:/sbin/nologin
rpc:x:32:32:Portmapper RPC user:/:/sbin/nologin
rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
nfsnobody:x:4294967294:4294967294:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
avahi-autoipd:x:100:101:avahi-autoipd:/var/lib/avahi-autoipd:/sbin/nologin
avahi:x:70:70:Avahi daemon:/:/sbin/nologin
apache:x:48:48:Apache:/var/www:/sbin/nologin
distcache:x:94:94:Distcache:/:/sbin/nologin
squid:x:23:23::/var/spool/squid:/sbin/nologin
webalizer:x:67:67:Webalizer:/var/www/usage:/sbin/nologin
ntp:x:38:38::/etc/ntp:/sbin/nologin
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
named:x:25:25:Named:/var/named:/sbin/nologin
xfs:x:43:43:X Font Server:/etc/X11/fs:/sbin/nologin
gdm:x:42:42::/var/gdm:/sbin/nologin
sabayon:x:86:86:Sabayon user:/home/sabayon:/sbin/nologin
dovecot:x:97:97:dovecot:/usr/libexec/dovecot:/sbin/nologin
amanda:x:33:6:Amanda user:/var/lib/amanda:/bin/bash
exim:x:93:93::/var/spool/exim:/sbin/nologin
mailman:x:41:41:GNU Mailing List Manager:/usr/lib/mailman:/sbin/nologin
ident:x:98:98::/home/ident:/sbin/nologin
pvm:x:24:24::/usr/share/pvm3:/bin/bash
quagga:x:92:92:Quagga routing suite:/var/run/quagga:/sbin/nologin
privoxy:x:73:73::/etc/privoxy:/sbin/nologin
radvd:x:75:75:radvd user:/:/sbin/nologin
uuidd:x:101:104:UUID generator helper daemon:/var/lib/libuuid:/sbin/nologin
cyrus:x:76:12:Cyrus IMAP Server:/var/lib/imap:/bin/bash
ldap:x:55:55:LDAP User:/var/lib/ldap:/bin/false
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
radiusd:x:95:95:radiusd user:/home/radiusd:/sbin/nologin
pegasus:x:66:65:tog-pegasus OpenPegasus WBEM/CIM services:/var/lib/Pegasus:/sbin/nologin
tomcat:x:91:91:Tomcat:/usr/share/tomcat5:/bin/sh
oracle:x:500:500::/home/oracle:/bin/bash

4.前面都是准备工作，真正的导入操作在此
ora10g@testdb183 /home/oracle$ sqlldr userid=sec/sec control=load_passwd.ctl log=load_passwd.log bad=load_passwd.bad discard=load_passwd.dsc

SQL*Loader: Release 10.2.0.3.0 - Production on Sun Aug 30 17:08:59 2009

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

Commit point reached - logical record count 56

5.如果成功导入，对应目录中只会有load_passwd.log文件，查看一下这个文件的内容，该文件详细地记录了导入过程涉及到的参数和导入结果
ora10g@testdb183 /home/oracle$ cat load_passwd.log

SQL*Loader: Release 10.2.0.3.0 - Production on Sun Aug 30 17:08:59 2009

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

Control File:   load_passwd.ctl
Data File:      load_passwd.ctl
  Bad File:     load_passwd.bad
  Discard File: load_passwd.dsc
 (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 LINUX_PASSWD, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ----------
P_USER_NAME                         FIRST     *   :       CHARACTER
P_PASSWORD                           NEXT     *   :       CHARACTER
P_UID                                NEXT     *   :       CHARACTER
P_GID                                NEXT     *   :       CHARACTER
P_DESCRIPTION                        NEXT     *   :       CHARACTER
P_MAIN_DIR                           NEXT     *   :       CHARACTER
P_SHELL                              NEXT     *   :       CHARACTER


Table LINUX_PASSWD:
  56 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:                 115584 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            56
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Aug 30 17:08:59 2009
Run ended on Sun Aug 30 17:08:59 2009

Elapsed time was:     00:00:00.08
CPU time was:         00:00:00.02
ora10g@testdb183 /home/oracle$

6.在成功导入之后，我们到数据库中查看一下 linux_passwd 表中的数据
ora10g@testdb183 /home/oracle$ sqlplus sec/sec

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Aug 30 17:10:17 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

sec@ORA10G> col P_USER_NAME for a13
sec@ORA10G> col P_PASSWORD for a4
sec@ORA10G> col P_UID for 99999999999
sec@ORA10G> col P_GID for 99999999999
sec@ORA10G> col P_DESCRIPTION for a42
sec@ORA10G> col P_MAIN_DIR for a22
sec@ORA10G> col P_SHELL for a15
sec@ORA10G> select * from linux_passwd;

P_USER_NAME   P_PA P_UID P_GID P_DESCRIPTION  P_MAIN_DIR             P_SHELL       
------------- ---- ----- ----- -------------- ---------------------- ---------------
root          x        0     0 root           /root                  /bin/bash     
bin           x        1     1 bin            /bin                   /sbin/nologin 
daemon        x        2     2 daemon         /sbin                  /sbin/nologin 
adm           x        3     4 adm            /var/adm               /sbin/nologin 
lp            x        4     7 lp             /var/spool/lpd         /sbin/nologin 
sync          x        5     0 sync           /sbin                  /bin/sync     
shutdown      x        6     0 shutdown       /sbin                  /sbin/shutdown
halt          x        7     0 halt           /sbin                  /sbin/halt    
mail          x        8    12 mail           /var/spool/mail        /sbin/nologin 
uucp          x       10    14 uucp           /var/spool/uucp        /sbin/nologin 
operator      x       11     0 operator       /root                  /sbin/nologin 
games         x       12   100 games          /usr/games             /sbin/nologin 
gopher        x       13    30 gopher         /var/gopher            /sbin/nologin 
ftp           x       14    50 FTP User       /var/ftp               /sbin/nologin 
nobody        x       99    99 Nobody         /                      /sbin/nologin 
nscd          x       28    28 NSCD Daemon    /                      /sbin/nologin 
vcsa          x       69    69 virtual consol /dev                   /sbin/nologin 
pcap          x       77    77                /var/arpwatch          /sbin/nologin 
mailnull      x       47    47                /var/spool/mqueue      /sbin/nologin 
smmsp         x       51    51                /var/spool/mqueue      /sbin/nologin 
rpc           x       32    32 Portmapper RPC /                      /sbin/nologin 
rpcuser       x       29    29 RPC Service Us /var/lib/nfs           /sbin/nologin 
nfsnobody     x    67294 67294 Anonymous NFS  /var/lib/nfs           /sbin/nologin 
sshd          x       74    74 Privilege-sepa /var/empty/sshd        /sbin/nologin 
dbus          x       81    81 System message /                      /sbin/nologin 
haldaemon     x       68    68 HAL daemon     /                      /sbin/nologin 
avahi-autoipd x      100   101 avahi-autoipd  /var/lib/avahi-autoipd /sbin/nologin 
avahi         x       70    70 Avahi daemon   /                      /sbin/nologin 
apache        x       48    48 Apache         /var/www               /sbin/nologin 
distcache     x       94    94 Distcache      /                      /sbin/nologin 
squid         x       23    23                /var/spool/squid       /sbin/nologin 
webalizer     x       67    67 Webalizer      /var/www/usage         /sbin/nologin 
ntp           x       38    38                /etc/ntp               /sbin/nologin 
postgres      x       26    26 PostgreSQL Ser /var/lib/pgsql         /bin/bash     
mysql         x       27    27 MySQL Server   /var/lib/mysql         /bin/bash     
named         x       25    25 Named          /var/named             /sbin/nologin 
xfs           x       43    43 X Font Server  /etc/X11/fs            /sbin/nologin 
gdm           x       42    42                /var/gdm               /sbin/nologin 
sabayon       x       86    86 Sabayon user   /home/sabayon          /sbin/nologin 
dovecot       x       97    97 dovecot        /usr/libexec/dovecot   /sbin/nologin 
amanda        x       33     6 Amanda user    /var/lib/amanda        /bin/bash     
exim          x       93    93                /var/spool/exim        /sbin/nologin 
mailman       x       41    41 GNU Mailing Li /usr/lib/mailman       /sbin/nologin 
ident         x       98    98                /home/ident            /sbin/nologin 
pvm           x       24    24                /usr/share/pvm3        /bin/bash     
quagga        x       92    92 Quagga routing /var/run/quagga        /sbin/nologin 
privoxy       x       73    73                /etc/privoxy           /sbin/nologin 
radvd         x       75    75 radvd user     /                      /sbin/nologin 
uuidd         x      101   104 UUID generator /var/lib/libuuid       /sbin/nologin 
cyrus         x       76    12 Cyrus IMAP Ser /var/lib/imap          /bin/bash     
ldap          x       55    55 LDAP User      /var/lib/ldap          /bin/false    
postfix       x       89    89                /var/spool/postfix     /sbin/nologin 
radiusd       x       95    95 radiusd user   /home/radiusd          /sbin/nologin 
pegasus       x       66    65 tog-pegasus Op /var/lib/Pegasus       /sbin/nologin 
tomcat        x       91    91 Tomcat         /usr/share/tomcat5     /bin/sh       
oracle        x      500   500                /home/oracle           /bin/bash     
                                                                                   
56 rows selected.                                                                                                            

7.OK，圆满的完成了实验目标。
这里演示的是一个很经典的SQL*Loader操作流程，任何SQL*Loader导入都可按照这个流程来完成。只是在一些细节上有区别，比如数据文件是否是放到一个单独的文件中（这个实验中数据是放到控制文件里的），各种各样的分隔符的特殊处理方式等等。

8.不可不看的扩展参考
【 SQL*Loader 参考一】在命令行窗口直接使用sqlldr就可以看到一个简略的帮助文档
ora10g@testdb183 /home/oracle$ sqlldr

SQL*Loader: Release 10.2.0.3.0 - Production on Sun Aug 30 16:10:32 2009

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)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- do parallel load                     (Default FALSE)
      file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
  readsize -- size of read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array  (Default 5000)
streamsize -- size of direct path stream buffer in bytes  (Default 256000)
multithreading -- use multithreading in direct path
 resumable -- enable or disable resumable for current session  (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache -- size (in entries) of date conversion cache  (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords.  An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'.  One may specify parameters by position before
but not after parameters specified by keywords.  For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

【 SQL*Loader 参考二】《Oracle SQL*Loader Version 10.2》这个文档很有参考价值
http://www.psoug.org/reference/sqlloader.html

【 SQL*Loader 参考三】Oralce官方文档《SQL*Loader》Chapter 6到Chapter 11六章的内容都是介绍SQL*Loader的，可见Oracle对这个工具的重视程度。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/part_ldr.htm#i436326

9.小结
SQLLDR优点：
1.迁移、备份和恢复数据的又一个有效手段
2.不同数据库之间进行数据迁移的非常方便而且通用的工具，避免类似EXP（EXPDP）/IMP（IMPDP）工具导致乱码问题；
3.从文本文件向数据库迁移的超级有效的手段；
4.速度快，尤其结合使用直接路径加载技术，这个技术可以跳过整个SQL引擎，同时避免undo和redo的生成，有效的提高数据 的 加载效率；
5.与外部表技术结合紧密。

SQLLDR缺点：
基本上没有什么缺点，不过，对较特别的类型数据，如LOB类型的数据导入过程需要注意的地方较多，有机会就此展开讨论一下。

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-613574/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-613574/