同事最近在忙数据一致性比对工作，需要对不同文本文件中的数据进行比对，有的文件较大，记录较多，如果用普通的文本编辑器打开的话，很显然，会很卡，甚至打不开。
基于此，可将该文本文件的数据导入到数据库中，在集合的层面进行比对。
那么如何将文本文件的数据导入到数据库中呢？在这里，主要利用了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_loaderdefault directory tmpaccess parameters(records delimited by newlinefields terminated by |( subsiddecimal external,servnumber char(20),subsprodid decimal external,prodid char(32),startdate date yyyymmddhh24miss,enddate date yyyymmddhh24miss,owner char(4)))location ());
注意，目录tmp必须存在，因为我是在scott用户下执行的，所以scott用户必须对该路径有读写权限。
第三、在scott用户下执行该建表语句。
第四、查看生成的外部表是否有问题
sql(*) from emp_load;count(*)
记录与wc-l查看的记录数吻合。
注意，建表过程中没有报错并不一定意味着数据已经成功加载在外部表中。必须通过查询外部表来判定数据是否已成功加载，倘若有错误提示，可参看当前目录下生成的日志文件，具体在本例中，是emp_load_2000.bad和emp_load_2000.log。
当然，外部表中的数据只能查询，不能做dml操作，譬如，随机删除表中的一条数据
sqlemp_load where rownum=1;error at line 1:ora-30657: operation not supported on external organized table
如果想对该外部表数据进行dml操作，可先将外部表的数据导入到内部表中。具体步骤如下：
sqltest emp_load ;table created.test emp_load;2899265 rows created.elapsed: 00:01:00.29sqltest where rownum100;test error at line 1:oramodify an object after modifying it in parallel;commit complete.sqltest where rownum100;
在这里，为了节省时间，我用了直接路径插入，可以看出，插入近300万数据，只用了1分左右的时间，考虑到我虚拟机上的数据库，只给它分配了300m的内存，加载的效率还是相当可观的。
sql> show parameter memorynametypevalue------------------------------------ ----------- ------------------------------hi_shared_memory_addressinteger0memory_max_targetbig integer 300mmemory_targetbig integer 300mshared_memory_addressinteger0
后来，测试了一下传统路径插入所消耗的时间，为了和直接路径插入进行比较，剔除sql解析，data buffer等因素的影响，清空了共享池和buffer cache，具体如下：
sql> conn /as sysdbaconnected.sql> alter system flush shared_pool;system altered.sql> alter system flush buffer_cache;system altered.sql> conn scott/tigerconnected.sql> set timing onsqltest emp_load;2899265 rows created.elapsed: 00:01:05.36
用传统路径插入（即会产生redo日志），耗时1分5秒，相对于直接路径插入，两者效率相差不大，看来还是数据量较小，不能明显的体现直接路径插入速度上的优势。
注意：在sql*plus中，，number字段的输出默认为10，这样会导致对于937116510102250300这样的数值，可能会显示为9.3712e+17，在这里，可通过set numwidth 18来显示完整的number字段的值。
本文永久更新链接地址：