【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 --

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值