【實驗】【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》這個文件很有參考價值
【SQL*Loader參考三】Oralce官方文件《SQL*Loader》Chapter 6到Chapter 11六章的內容都是介紹SQL*Loader的，可見Oracle對這個工具的重視程度。
9.小結
SQLLDR優點：
1.遷移、備份和恢復資料的又一個有效手段
2.不同資料庫之間進行資料遷移的非常方便而且通用的工具，避免類似EXP（EXPDP）/IMP（IMPDP）工具導致亂碼問題；
3.從文字檔案向資料庫遷移的超級有效的手段；
4.速度快，尤其結合使用直接路徑載入技術，這個技術可以跳過整個SQL引擎，同時避免undo和redo的生成，有效的提高資料的載入效率；
5.與外部表技術結合緊密。
SQLLDR缺點：
基本上沒有什麼缺點，不過，對較特別的型別資料，如LOB型別的資料匯入過程需要注意的地方較多，有機會就此展開討論一下。
-- The End --
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》這個文件很有參考價值
【SQL*Loader參考三】Oralce官方文件《SQL*Loader》Chapter 6到Chapter 11六章的內容都是介紹SQL*Loader的，可見Oracle對這個工具的重視程度。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【實驗】【SQL*Loader】使用SQLLDR將資料載入到CLOB欄位SQL
- 【SQL*Loader】sqlldr匯入SQL
- Oracle 的資料匯入匯出及 Sql Loader (sqlldr) 的用法OracleSQL
- 關於 Oracle 的資料匯入匯出及 Sql Loader (sqlldr) 的用法OracleSQL
- Oracle工具之sqlldr的使用--如何將文字檔案或Excel中的資料匯入資料庫OracleSQLExcel資料庫
- PHP 匯入資料庫 sql 檔案PHP資料庫SQL
- 文字檔案用sqlldr工具匯入到oracel資料庫中SQL資料庫
- oracle資料庫使用sqlldr命令匯入txt資料Oracle資料庫SQL
- 採用sqlldr定時將文字檔案載入進入資料庫SQL資料庫
- Mysql資料庫使用Navicat Mysql匯入sql檔案報錯MySql資料庫
- 資料匯入SQLLDRSQL
- 使用SQL*Loader匯入CLOB和BLOB資料使用案例SQL
- 轉載：利用SQL*Loader將 Excel 資料匯出到資料庫中SQLExcel資料庫
- SQLSERVER匯出TXT文字檔案，ORACLE SQL LOADER匯入TXT文字檔案SQLServerOracle
- 使用oracle sqlldr匯入文字資料的例子OracleSQL
- 【匯入匯出】將資料匯入到其他使用者
- 用SQL Loader將Excel資料匯出到OracleSQLExcelOracle
- 如何把 .csv 的檔案匯入資料庫SQL SERVER 中！資料庫SQLServer
- 從cmd中匯入.SQL檔案並建立資料庫SQL資料庫
- SQL loader 匯入csv格式的表格檔案的一個例子SQL
- Oracle SQL Loader(sqlldr)OracleSQL
- sqlldr 匯入資料範例SQL
- 資料庫遠端檔案匯入資料庫
- Oracle：從SQL檔案批量匯入資料OracleSQL
- 透過sqlldr匯入到sys使用者SQL
- 通過sqlldr匯入到sys使用者SQL
- SQL資料庫的匯入和匯出SQL資料庫
- pl/sql developer將excel資料匯入到資料庫中SQLDeveloperExcel資料庫
- 使用sqlldr匯入文字資料到oracleSQLOracle
- 關於資料表結構sql檔案匯入mysql資料庫的問題？MySql資料庫
- sqlldr批量匯入匯出資料測試SQL
- sqlldr批次匯入匯出資料測試SQL
- 教你如何將二進位制檔案匯入到資料庫資料庫
- 通過SQLLDR匯入LOB資料SQL
- 使用xml檔案,做資料的匯入,匯出 (轉)XML
- 將XML匯入資料庫XML資料庫
- 談談資料從sql server資料庫匯入mysql資料庫的體驗(轉)Server資料庫MySql
- 如何將外部資料庫 匯入到系統的SQL中資料庫SQL