如何利用Oracle外部表导入文本文件的数据

同事最近在忙数据一致性比对工作，需要对不同文本文件中的数据进行比对，有的文件较大，记录较多，如果用普通的文本编辑器打开的话，很显然，会很卡，甚至打不开。

基于此，可将该文本文件的数据导入到数据库中，在集合的层面进行比对。

那么如何将文本文件的数据导入到数据库中呢？在这里，主要利用了Oracle的外部表特性。

Oracle外部表支持两种类型的驱动：一种是ORACLE_LOADER，外部表的数据必须来源于文件文件，另一种则是ORACLE_DATAPUMP，外部表的数据必须是二进制dump文件，该dump文件是先前将Oracle内部表的数据导入到外部表中填充的文件。很显然，Oracle希望将数据保留在数据库内部进行处理。

首先，我们来看一下该文本文件的大小及记录。

[oracle@node2 ~]$ du -sm P_20150626010000_2002371.0003479598 
274    P_20150626010000_2002371.0003479598
[oracle@node2 ~]$ wc -l P_20150626010000_2002371.0003479598 
2899265 P_20150626010000_2002371.0003479598

从上面的输出可以看出，该文件274M，有2899265条记录。

其次，构建创建外部表语句。

CREATE TABLE emp_load
   (subsid number(18),
    servnumber VARCHAR2(20 CHAR),
    subsprodid NUMBER(18),
    prodid VARCHAR2(32 CHAR),
    startdate date,
    enddate  date, 
    owner VARCHAR2(4 CHAR))
ORGANIZATION EXTERNAL
   (TYPE ORACLE_LOADER
    DEFAULT DIRECTORY tmp
    ACCESS PARAMETERS
      (RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY "|"
             ( subsid      DECIMAL EXTERNAL,
               servnumber  CHAR(20),
               subsprodid  DECIMAL EXTERNAL,
               prodid  CHAR(32),
                startdate date "yyyymmddhh24miss",
               enddate date "yyyymmddhh24miss",
               owner   CHAR(4) 
              )
      )
    LOCATION ('P_20150626010000_2002371.0003479598')
   );

注意，目录tmp必须存在，因为我是在scott用户下执行的，所以scott用户必须对该路径有读写权限。

第三、在scott用户下执行该建表语句。

第四、查看生成的外部表是否有问题

SQL> select count(*) from emp_load;

      COUNT(*)
------------------
       2899265

记录与wc-l查看的记录数吻合。

注意，建表过程中没有报错并不一定意味着数据已经成功加载在外部表中。必须通过查询外部表来判定数据是否已成功加载，倘若有错误提示，可参看当前目录下生成的日志文件，具体在本例中，是EMP_LOAD_2000.bad和EMP_LOAD_2000.log。

当然，外部表中的数据只能查询，不能做DML操作，譬如，随机删除表中的一条数据

SQL> delete from emp_load where rownum=1;
delete from emp_load where rownum=1
            *
ERROR at line 1:
ORA-30657: operation not supported on external organized table

如果想对该外部表数据进行DML操作，可先将外部表的数据导入到内部表中。具体步骤如下：

SQL> create table test as select * from emp_load where 1=0;

Table created.

Elapsed: 00:00:00.26
SQL> INSERT /*+ APPEND */ INTO test select * from emp_load;

2899265 rows created.

Elapsed: 00:01:00.29
SQL> select * from test where rownum<=100;
select * from test where rownum<=100
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.10
SQL> commit;

Commit complete.

Elapsed: 00:00:00.07
SQL> select * from test where rownum<=100;

在这里，为了节省时间，我用了直接路径插入，可以看出，插入近300万数据，只用了1分左右的时间，考虑到我虚拟机上的数据库，只给它分配了300M的内存，加载的效率还是相当可观的。

SQL> show parameter memory

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address         integer     0
memory_max_target             big integer 300M
memory_target                 big integer 300M
shared_memory_address             integer     0

注意：在SQL*PLUS中，number字段的输出默认为10，这样会导致对于937116510102250300这样的数值，可能会显示为9.3712E+17，在这里，可通过set numwidth 18来显示完整的number字段的值。

====================================================================

create table 表名(

列名1,列名2，......

)

organization external ###说明创建外部表

(

type 访问类型 ###一般是 ORACLE_LOADER ,ORACLE_DATAPUMP

default directory 路径名 ####指定默认目录对象

access parameter ( ###数据源文件与表中行之间的映射关系

records delimited by 设置分隔符 ###一般是newline

[badfile 路径名:'文件名'] ##存放错误日志的目录和文件名

[logfile 路径名:'文件名'] ##日志的目录和文件名

[discardfile 路径名：'文件名'] ###废弃文件存放的目录和文件名

fields terminated by '分隔符' ###设置文件中字段的分隔符

[ missing field values are null] ##无值字段的处理

[reject rows with all null fields] ##无值行的处理

( 列名1, 列名2, .....)

)

location (路径名:'数据文件名')

)

[reject limit（或unlimited）数值 ] ###设置多少行转换失败时返回oracle错误，默认为0

[parallel]

==================================================

经常有需求向表中导入大量的数据，使用insert不靠谱，太慢
oracle提供了sqlldr的工具

也有时需要讲数据导入到文本，oracle的spool可以轻松实现

方便的实现oracle导出数据到txt、txt导入数据到oracle

一、导出数据到txt

这里用all_objects表做测试

SQL> desc all_objects; Name Null? Type ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30) OBJECT_NAME                               NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID                                 NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP                                          VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY                                          VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)

拿object_id,object_name做导出、导入测试
这里需要一些设置满足数据导出的样式

vi exp_table.sql

set line 1000         --设置行的长度
set pagesize 0        --输出不换页
set feedback off      --默认的当一条sql发出的时候，oracle会给一个反馈，比如说创建表的时候，如果成功命令行会返回类似：Table created的反馈,off后不显示反馈
set heading off       --不显示表头信息
set trimspool on      --如果trimspool设置为on，将移除spool文件中的尾部空
set trims on          --去掉空字符
set echo off;　　　　   --显示start启动的脚本中的每个sql命令，缺省为on
set colsep '|'         --设置分隔符
set termout off        --不在屏幕上显示结果
spool db1.txt          --记录数据到db1.txt
select object_id,object_name from all_objects; --导出数据语句 spool off              --收集完毕
exit

一切就绪后导出数据

[oracle@centos5 ~]$ sqlplus test/test @exp_table.sql SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:35:14 2013 Copyright (c) 1982, 2007, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@centos5 ~]$ sed -i 's/ //g' db1.txt --可选，去除每行开头部分的空格 [oracle@centos5 ~]$ more db1.txt 20|ICOL$ 44|I_USER1 28|CON$ 15|UNDO$ 29|C_COBJ# 3|I_OBJ# 25|PROXY_ROLE_DATA$

导出后检查数据的记录数是否正确

[oracle@centos5 ~]$ cat db1.txt |wc -l 49988
[oracle@centos5 ~]$ sqlplus test/test SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:36:21 2013 Copyright (c) 1982, 2007, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select count(*) from all_objects; COUNT(*) ----------
     49988 --数据正确

二、从txt导入数据到oracle

sqlldr是通过一个control文件设定后，从文本导入数据

建立一张测试表

SQL> create table tb_sqlldr (id number,name varchar2(50)); Table created.

建立一个control文件
vi tb_sqlldr.ctl

load data                 
infile 'db1.txt'            --数据来源文本
append into table tb_sqlldr    --数据导入到表tb_sqldr中，导入方式为追加，如果想覆盖
fields terminated by "|"    --4、字段终止于X'09'，是一个制表符(tab)
(id,name)                    --定义对应的字段名称，注意顺序

导入数据分成四种模式，可以根据需求选择：

APPEND // 原先的表有数据就加在后面
INSERT // 装载空表如果原先的表有数据 sqlloader会停止默认值
REPLACE // 原先的表有数据原先的数据会全部删除
TRUNCATE // 指定的内容和replace的相同会用truncate语句删除现存数据

执行导入操作

sqlldr userid=test/test control=tb_sqlldr.ctl

差不多5w的数据短短2s解决

执行导入后验证数据

SQL> select count(*) from tb_sqlldr; COUNT(*) ----------
     49988

导入成功

再执行一次导入操作，由于设置为追加

SQL> select count(*) from tb_sqlldr; COUNT(*) ----------
     99976

记录翻倍

sqlldr还有很多参数供选择，比如log、bad这些，查看帮助即可

[oracle@centos5 ~]$ sqlldr

SQL*Loader: Release 10.2.0.4.0 - Production on Thu Jun 13 17:07:26 2013 Copyright (c) 1982, 2007, 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.

秒客网

如何利用Oracle外部表导入文本文件的数据

相关文章