同事最近在忙數據一致性比對工作，需要對不同文本文件中的數據進行比對，有的文件較大，記錄較多，如果用普通的文本編輯器打開的話，很顯然，會很卡，甚至打不開。
基於此，可將該文本文件的數據導入到數據庫中，在集合的層面進行比對。
那麼如何將文本文件的數據導入到數據庫中呢？在這裡，主要利用了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字段的值。
