如果想使用外部表技术完成数据的加载,一般的步骤如下:
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-620556/