【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語句SQL
- excel表結構生成powerDesigner模型，生成建表語句sqlExcel模型SQL
- jsqlparser使用記錄---生成sql語句JSSQL
- 通用輔助生成: 使用任意輔助模型加速解碼模型
- T-SQL——數字輔助表SQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- 4.3.2 關於使用SQL語句建立CDBSQL
- 英特爾 Gaudi 加速輔助生成
- 使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用SQL
- create table 使用select查詢語句建立表的方法分享
- Redshift__在一個外部架構下建立外部表後，其他外部架構也自動生成了一樣的外部表架構
- 建立Laravel自定義Helper輔助方法Laravel
- Laravel 使用 sql 語句 和 sql 檔案 來建立執行資料庫遷移LaravelSQL資料庫
- 統計報表 -- sql統計語句SQL
- 兩表聯查修改的sql語句SQL
- SQL單表查詢語句總結SQL
- SQL語句SQL
- EFCore常規操作生成的SQL語句一覽SQL
- 常見的SQL語句（建立、刪除、切換）SQL
- SqlSugarClient 程式碼優先建表， 根據給定的實體類，建立SQL語句, 之後建立MySQL表SqlSugarclientMySql
- Fastapi sqlalchemy DBApi 直接使用sql語句ASTAPISQL
- 更快的輔助生成: 動態推測
- 4.3.3 使用CREATE DATABASE語句建立CDBDatabase
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- 自定義註解例項實現SQL語句生成SQL
- SQL SELECT 語句SQL
- sql常用語句SQL
- oracle匯出使用者、表空間和角色的建立語句Oracle
- SQL Server如何匯出db所有使用者許可權建立語句SQLServer
- 複製表結構和資料SQL語句SQL
- Mysql跨表更新 多表update sql語句總結MySql
- [20210112]ashtop查詢特定表的SQL語句.txtSQL
- Hibernate/JPA如何保證不生成多餘的SQL語句？SQL
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- 使用SQL語句將資料庫中的兩個表合併成一張表SQL資料庫
- 1.3. SQL 語句SQL