【SQLLDR】使用SQL*Loader輔助生成外部表建立語句
如果想使用外部表技術完成資料的載入，一般的步驟如下：
1.因為外部表需要directory物件，首先需要建立一個directory資料庫物件；
2.需要對建立外部表所需的引數和格式非常瞭解，以便完成外部表建立語句的編寫；
3.編寫直接路徑載入方式的insert語句完成從外部表到目標表的載入任務。
從上面所述步驟可見需要準備很多指令碼，比較繁瑣，此時如果我們利用SQL*Loader的“external_table=generate_only”引數，則花在準備工作上的時間將大大的減少。透過這個實驗給大家展示一下利用SQLLDR快速生成外部表並載入資料的過程，供大家參考。
這個實驗將沿用之前《【實驗】【SQL*Loader】使用SQLLDR將Linux的使用者檔案passwd匯入資料庫》實驗中的例子。
連結參考：http://space.itpub.net/519536/viewspace-613574
1.建立待載入的目標表linux_passwd
ora10g@secooler /home/oracle$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Nov 25 10:00:14 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> 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.
2.準備SQLLDR的控制檔案
ora10g@secooler /home/oracle$ cat load_passwd.ctl
LOAD DATA
INFILE *
INTO TABLE linux_passwd
REPLACE
FIELDS TERMINATED BY ':'
( p_user_name ,
p_password ,
p_uid ,
p_gid ,
p_description ,
p_main_dir ,
p_shell
)
3.使用SQLLDR的“external_table=generate_only”引數生成外部表建立語句
ora10g@secooler /home/oracle$ sqlldr sec/sec load_passwd.ctl external_table=generate_only
SQL*Loader: Release 10.2.0.3.0 - Production on Wed Nov 25 10:00:30 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
4.檢視生成的“load_passwd.log”檔案便可得到清晰的外部表建立和使用語句
ora10g@secooler /home/oracle$ cat load_passwd.log
SQL*Loader: Release 10.2.0.3.0 - Production on Wed Nov 25 10:00:30 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: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
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
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
(
"P_USER_NAME" VARCHAR2(20),
"P_PASSWORD" VARCHAR2(20),
"P_UID" NUMBER(20),
"P_GID" NUMBER(20),
"P_DESCRIPTION" VARCHAR2(100),
"P_MAIN_DIR" VARCHAR2(100),
"P_SHELL" VARCHAR2(50)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'load_passwd.bad'
LOGFILE 'load_passwd.log_xt'
READSIZE 1048576
SKIP 14
FIELDS TERMINATED BY ":" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"P_USER_NAME" CHAR(255)
TERMINATED BY ":",
"P_PASSWORD" CHAR(255)
TERMINATED BY ":",
"P_UID" CHAR(255)
TERMINATED BY ":",
"P_GID" CHAR(255)
TERMINATED BY ":",
"P_DESCRIPTION" CHAR(255)
TERMINATED BY ":",
"P_MAIN_DIR" CHAR(255)
TERMINATED BY ":",
"P_SHELL" CHAR(255)
TERMINATED BY ":"
)
)
location
(
'load_passwd.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO LINUX_PASSWD
(
P_USER_NAME,
P_PASSWORD,
P_UID,
P_GID,
P_DESCRIPTION,
P_MAIN_DIR,
P_SHELL
)
SELECT
"P_USER_NAME",
"P_PASSWORD",
"P_UID",
"P_GID",
"P_DESCRIPTION",
"P_MAIN_DIR",
"P_SHELL"
FROM "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Wed Nov 25 10:00:30 2009
Run ended on Wed Nov 25 10:00:30 2009
Elapsed time was: 00:00:00.08
CPU time was: 00:00:00.01
5.我們來按照上面生成的指令碼內容實操一下，體會一下外部表的建立和使用過程。
1）建立directory
sec@ora10g> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle';
Directory created.
2）建立外部表
僅需對外部表建立語句稍作修改（將location由原來的“load_passwd.ctl”修改為“passwd”）即可。
sec@ora10g> r
1 CREATE TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
2 (
3 "P_USER_NAME" VARCHAR2(20),
4 "P_PASSWORD" VARCHAR2(20),
5 "P_UID" NUMBER(20),
6 "P_GID" NUMBER(20),
7 "P_DESCRIPTION" VARCHAR2(100),
8 "P_MAIN_DIR" VARCHAR2(100),
9 "P_SHELL" VARCHAR2(50)
10 )
11 ORGANIZATION external
12 (
13 TYPE oracle_loader
14 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
15 ACCESS PARAMETERS
16 (
17 RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
18 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'load_passwd.bad'
19 LOGFILE 'load_passwd.log_xt'
20 READSIZE 1048576
21 SKIP 14
22 FIELDS TERMINATED BY ":" LDRTRIM
23 REJECT ROWS WITH ALL NULL FIELDS
24 (
25 "P_USER_NAME" CHAR(255)
26 TERMINATED BY ":",
27 "P_PASSWORD" CHAR(255)
28 TERMINATED BY ":",
29 "P_UID" CHAR(255)
30 TERMINATED BY ":",
31 "P_GID" CHAR(255)
32 TERMINATED BY ":",
33 "P_DESCRIPTION" CHAR(255)
34 TERMINATED BY ":",
35 "P_MAIN_DIR" CHAR(255)
36 TERMINATED BY ":",
37 "P_SHELL" CHAR(255)
38 TERMINATED BY ":"
39 )
40 )
41 location
42 (
43 'passwd'
44 )
45* )REJECT LIMIT UNLIMITED
Table created.
3）檢視一下外部表的內容
sec@ora10g> set lin 200
sec@ora10g> col P_USER_NAME for a13
sec@ora10g> col P_PASSWORD for a2
sec@ora10g> col P_UID for 9999999999
sec@ora10g> col P_GID for 9999999999
sec@ora10g> col P_DESCRIPTION for a42
sec@ora10g> col P_MAIN_DIR for a22
sec@ora10g> col P_SHELL for a26
sec@ora10g> select * from SYS_SQLLDR_X_EXT_LINUX_PASSWD;
P_USER_NAME P_ P_UID P_GID P_DESCRIPTION P_MAIN_DIR P_SHELL
------------- -- ----------- ----------- ------------------------------------------ ---------------------- -------------
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
rpc x 32 32 Portmapper RPC user / /sbin/nologin
mailnull x 47 47 /var/spool/mqueue /sbin/nologin
smmsp x 51 51 /var/spool/mqueue /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
distcache x 94 94 Distcache / /sbin/nologin
ntp x 38 38 /etc/ntp /sbin/nologin
apache x 48 48 Apache /var/www /sbin/nologin
postgres x 26 26 PostgreSQL Server /var/lib/pgsql /bin/bash
webalizer x 67 67 Webalizer /var/www/usage /sbin/nologin
squid x 23 23 /var/spool/squid /sbin/nologin
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
uuidd x 101 104 UUID generator helper daemon /var/lib/libuuid /sbin/nologin
exim x 93 93 /var/spool/exim /sbin/nologin
amanda x 33 6 Amanda user /var/lib/amanda /bin/bash
ldap x 55 55 LDAP User /var/lib/ldap /bin/false
mailman x 41 41 GNU Mailing List Manager /usr/lib/mailman /sbin/nologin
postfix x 89 89 /var/spool/postfix /sbin/nologin
pvm x 24 24 /usr/share/pvm3 /bin/bash
privoxy x 73 73 /etc/privoxy /sbin/nologin
quagga x 92 92 Quagga routing suite /var/run/quagga /sbin/nologin
radvd x 75 75 radvd user / /sbin/nologin
cyrus x 76 12 Cyrus IMAP Server /var/lib/imap /bin/bash
ident x 98 98 /home/ident /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
43 rows selected.
4）使用外部表以直接路徑載入的方式填充目標表LINUX_PASSWD
sec@ora10g> INSERT /*+ append */ INTO LINUX_PASSWD
2 (
3 P_USER_NAME,
4 P_PASSWORD,
5 P_UID,
6 P_GID,
7 P_DESCRIPTION,
8 P_MAIN_DIR,
9 P_SHELL
10 )
11 SELECT
12 "P_USER_NAME",
13 "P_PASSWORD",
14 "P_UID",
15 "P_GID",
16 "P_DESCRIPTION",
17 "P_MAIN_DIR",
18 "P_SHELL"
19 FROM "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
20 /
43 rows created.
sec@ora10g> commit;
Commit complete.
5）查詢裝載之後的LINUX_PASSWD表
sec@ora10g> select * from LINUX_PASSWD;
內容與上面外部表查詢的內容一致，這裡不再贅述。
6）刪除外部表和directory
生成的指令碼中也同時包含了刪除的SQL語句，可謂無微不至。
sec@ora10g> DROP TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD";
Table dropped.
sec@ora10g> DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000;
Directory dropped.
6.小結
透過上面的實驗可見，僅需一個待載入的表定義和SQLLDR控制檔案，便可利用SQL*Loader的“external_table=generate_only”引數大大簡化建立外部表指令碼的過程，Oracle提供的手段很豐富也很人性化。
透過外部表在資料庫與作業系統裡的平文字檔案之間架起了友誼的橋樑，從今兒以後一切恩怨都將在資料庫內部了斷。
另外，使用外部表結合直接路徑載入技術可以非常高效的完成資料載入工作。
Good luck.
-- The End --
1.因為外部表需要directory物件，首先需要建立一個directory資料庫物件；
2.需要對建立外部表所需的引數和格式非常瞭解，以便完成外部表建立語句的編寫；
3.編寫直接路徑載入方式的insert語句完成從外部表到目標表的載入任務。
從上面所述步驟可見需要準備很多指令碼，比較繁瑣，此時如果我們利用SQL*Loader的“external_table=generate_only”引數，則花在準備工作上的時間將大大的減少。透過這個實驗給大家展示一下利用SQLLDR快速生成外部表並載入資料的過程，供大家參考。
這個實驗將沿用之前《【實驗】【SQL*Loader】使用SQLLDR將Linux的使用者檔案passwd匯入資料庫》實驗中的例子。
連結參考：http://space.itpub.net/519536/viewspace-613574
1.建立待載入的目標表linux_passwd
ora10g@secooler /home/oracle$ sqlplus sec/sec
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Nov 25 10:00:14 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> 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.
2.準備SQLLDR的控制檔案
ora10g@secooler /home/oracle$ cat load_passwd.ctl
LOAD DATA
INFILE *
INTO TABLE linux_passwd
REPLACE
FIELDS TERMINATED BY ':'
( p_user_name ,
p_password ,
p_uid ,
p_gid ,
p_description ,
p_main_dir ,
p_shell
)
3.使用SQLLDR的“external_table=generate_only”引數生成外部表建立語句
ora10g@secooler /home/oracle$ sqlldr sec/sec load_passwd.ctl external_table=generate_only
SQL*Loader: Release 10.2.0.3.0 - Production on Wed Nov 25 10:00:30 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
4.檢視生成的“load_passwd.log”檔案便可得到清晰的外部表建立和使用語句
ora10g@secooler /home/oracle$ cat load_passwd.log
SQL*Loader: Release 10.2.0.3.0 - Production on Wed Nov 25 10:00:30 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: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
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
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
(
"P_USER_NAME" VARCHAR2(20),
"P_PASSWORD" VARCHAR2(20),
"P_UID" NUMBER(20),
"P_GID" NUMBER(20),
"P_DESCRIPTION" VARCHAR2(100),
"P_MAIN_DIR" VARCHAR2(100),
"P_SHELL" VARCHAR2(50)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'load_passwd.bad'
LOGFILE 'load_passwd.log_xt'
READSIZE 1048576
SKIP 14
FIELDS TERMINATED BY ":" LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"P_USER_NAME" CHAR(255)
TERMINATED BY ":",
"P_PASSWORD" CHAR(255)
TERMINATED BY ":",
"P_UID" CHAR(255)
TERMINATED BY ":",
"P_GID" CHAR(255)
TERMINATED BY ":",
"P_DESCRIPTION" CHAR(255)
TERMINATED BY ":",
"P_MAIN_DIR" CHAR(255)
TERMINATED BY ":",
"P_SHELL" CHAR(255)
TERMINATED BY ":"
)
)
location
(
'load_passwd.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO LINUX_PASSWD
(
P_USER_NAME,
P_PASSWORD,
P_UID,
P_GID,
P_DESCRIPTION,
P_MAIN_DIR,
P_SHELL
)
SELECT
"P_USER_NAME",
"P_PASSWORD",
"P_UID",
"P_GID",
"P_DESCRIPTION",
"P_MAIN_DIR",
"P_SHELL"
FROM "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Wed Nov 25 10:00:30 2009
Run ended on Wed Nov 25 10:00:30 2009
Elapsed time was: 00:00:00.08
CPU time was: 00:00:00.01
5.我們來按照上面生成的指令碼內容實操一下，體會一下外部表的建立和使用過程。
1）建立directory
sec@ora10g> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle';
Directory created.
2）建立外部表
僅需對外部表建立語句稍作修改（將location由原來的“load_passwd.ctl”修改為“passwd”）即可。
sec@ora10g> r
1 CREATE TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
2 (
3 "P_USER_NAME" VARCHAR2(20),
4 "P_PASSWORD" VARCHAR2(20),
5 "P_UID" NUMBER(20),
6 "P_GID" NUMBER(20),
7 "P_DESCRIPTION" VARCHAR2(100),
8 "P_MAIN_DIR" VARCHAR2(100),
9 "P_SHELL" VARCHAR2(50)
10 )
11 ORGANIZATION external
12 (
13 TYPE oracle_loader
14 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
15 ACCESS PARAMETERS
16 (
17 RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
18 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'load_passwd.bad'
19 LOGFILE 'load_passwd.log_xt'
20 READSIZE 1048576
21 SKIP 14
22 FIELDS TERMINATED BY ":" LDRTRIM
23 REJECT ROWS WITH ALL NULL FIELDS
24 (
25 "P_USER_NAME" CHAR(255)
26 TERMINATED BY ":",
27 "P_PASSWORD" CHAR(255)
28 TERMINATED BY ":",
29 "P_UID" CHAR(255)
30 TERMINATED BY ":",
31 "P_GID" CHAR(255)
32 TERMINATED BY ":",
33 "P_DESCRIPTION" CHAR(255)
34 TERMINATED BY ":",
35 "P_MAIN_DIR" CHAR(255)
36 TERMINATED BY ":",
37 "P_SHELL" CHAR(255)
38 TERMINATED BY ":"
39 )
40 )
41 location
42 (
43 'passwd'
44 )
45* )REJECT LIMIT UNLIMITED
Table created.
3）檢視一下外部表的內容
sec@ora10g> set lin 200
sec@ora10g> col P_USER_NAME for a13
sec@ora10g> col P_PASSWORD for a2
sec@ora10g> col P_UID for 9999999999
sec@ora10g> col P_GID for 9999999999
sec@ora10g> col P_DESCRIPTION for a42
sec@ora10g> col P_MAIN_DIR for a22
sec@ora10g> col P_SHELL for a26
sec@ora10g> select * from SYS_SQLLDR_X_EXT_LINUX_PASSWD;
P_USER_NAME P_ P_UID P_GID P_DESCRIPTION P_MAIN_DIR P_SHELL
------------- -- ----------- ----------- ------------------------------------------ ---------------------- -------------
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
rpc x 32 32 Portmapper RPC user / /sbin/nologin
mailnull x 47 47 /var/spool/mqueue /sbin/nologin
smmsp x 51 51 /var/spool/mqueue /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
distcache x 94 94 Distcache / /sbin/nologin
ntp x 38 38 /etc/ntp /sbin/nologin
apache x 48 48 Apache /var/www /sbin/nologin
postgres x 26 26 PostgreSQL Server /var/lib/pgsql /bin/bash
webalizer x 67 67 Webalizer /var/www/usage /sbin/nologin
squid x 23 23 /var/spool/squid /sbin/nologin
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
uuidd x 101 104 UUID generator helper daemon /var/lib/libuuid /sbin/nologin
exim x 93 93 /var/spool/exim /sbin/nologin
amanda x 33 6 Amanda user /var/lib/amanda /bin/bash
ldap x 55 55 LDAP User /var/lib/ldap /bin/false
mailman x 41 41 GNU Mailing List Manager /usr/lib/mailman /sbin/nologin
postfix x 89 89 /var/spool/postfix /sbin/nologin
pvm x 24 24 /usr/share/pvm3 /bin/bash
privoxy x 73 73 /etc/privoxy /sbin/nologin
quagga x 92 92 Quagga routing suite /var/run/quagga /sbin/nologin
radvd x 75 75 radvd user / /sbin/nologin
cyrus x 76 12 Cyrus IMAP Server /var/lib/imap /bin/bash
ident x 98 98 /home/ident /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
43 rows selected.
4）使用外部表以直接路徑載入的方式填充目標表LINUX_PASSWD
sec@ora10g> INSERT /*+ append */ INTO LINUX_PASSWD
2 (
3 P_USER_NAME,
4 P_PASSWORD,
5 P_UID,
6 P_GID,
7 P_DESCRIPTION,
8 P_MAIN_DIR,
9 P_SHELL
10 )
11 SELECT
12 "P_USER_NAME",
13 "P_PASSWORD",
14 "P_UID",
15 "P_GID",
16 "P_DESCRIPTION",
17 "P_MAIN_DIR",
18 "P_SHELL"
19 FROM "SYS_SQLLDR_X_EXT_LINUX_PASSWD"
20 /
43 rows created.
sec@ora10g> commit;
Commit complete.
5）查詢裝載之後的LINUX_PASSWD表
sec@ora10g> select * from LINUX_PASSWD;
內容與上面外部表查詢的內容一致，這裡不再贅述。
6）刪除外部表和directory
生成的指令碼中也同時包含了刪除的SQL語句，可謂無微不至。
sec@ora10g> DROP TABLE "SYS_SQLLDR_X_EXT_LINUX_PASSWD";
Table dropped.
sec@ora10g> DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000;
Directory dropped.
6.小結
透過上面的實驗可見，僅需一個待載入的表定義和SQLLDR控制檔案，便可利用SQL*Loader的“external_table=generate_only”引數大大簡化建立外部表指令碼的過程，Oracle提供的手段很豐富也很人性化。
透過外部表在資料庫與作業系統裡的平文字檔案之間架起了友誼的橋樑，從今兒以後一切恩怨都將在資料庫內部了斷。
另外，使用外部表結合直接路徑載入技術可以非常高效的完成資料載入工作。
Good luck.
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-620556/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用SQL*Loader建立外部表之二SQL
- 使用SQL*Loader建立外部表之一SQL
- 測試TOM=SQLLDR生成外部表SQL
- 使用sqlloader控制檔案生成外部表建立語句的一個例子SQL
- sql語句建立表SQL
- 【Tips】使用SQL生成外來鍵的SQL建立語句SQL
- Oracle SQL Loader(sqlldr)OracleSQL
- SQL語句圖表生成工具ChartSQLSQL
- 【SQL*Loader】sqlldr匯入SQL
- oracle sqlldr 與 外部表OracleSQL
- 定時生成分月表sql語句SQL
- 【PL/SQL】使用變數傳遞方法生成表更名的SQL語句SQL變數
- Oracle SQL Loader(sqlldr)+ Externale TablesOracleSQL
- jsqlparser使用記錄---生成sql語句JSSQL
- 測試TOM=SQLLDR使用CASE語句SQL
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- 4.3.2 關於使用SQL語句建立CDBSQL
- 透過sql語句建立表時指定表空間的語法SQL
- sql語句建立日曆SQL
- Sql建表語句SQL
- sql 建表語句SQL
- 外部表筆記一loader筆記
- 使用SQL MERGE語句組合表SQL
- OCA題目深入瞭解繼續，SQL*Loader和外部表SQL
- 執行sql語句給外部變數賦值SQL變數賦值
- 用SQL*Loader載入外部資料SQL
- 自動生成sqlldr 控制檔案的指令碼（Script To Generate SQL*Loader Control File）SQL指令碼
- sqlldr中使用條件裝載及作為外部表訪問SQL
- ORACLE_LOADER外部表簡單案例Oracle
- 【實驗】【SQL*Loader】使用SQLLDR將資料載入到CLOB欄位SQL
- 根據DELTA自動生成SQL語句SQL
- 使用Preprocessor前處理器語句對外部表進行介入處理
- Redshift__在一個外部架構下建立外部表後，其他外部架構也自動生成了一樣的外部表架構
- 使用SQL語句獲取SQLite中的表定義SQLite
- 用sql語句建立表的時候給列指定說明(轉)SQL
- sql 中的with 語句使用SQL
- SQL語句查詢表結構SQL