如何利用Oracle外部表格匯入文字檔的資料

標籤：

同事最近在忙資料一致性比對工作，需要對不同文字檔中的資料進行比對，有的檔案較大，記錄較多，如果用普通的文字編輯器開啟的話，很顯然，會很卡，甚至打不開。

基於此，可將該文字檔的資料匯入到資料庫中，在集合的層面進行比對。

那麼如何將文字檔的資料匯入到資料庫中呢？在這裡，主要利用了Oracle的外部表格特性。

Oracle外部表格支援兩種類型的驅動：一種是ORACLE_LOADER，外部表格的資料必須來源於檔案檔案，另一種則是ORACLE_DATAPUMP，外部表格的資料必須是二進位dump檔案，該dump檔案是先前將Oracle內部表的資料匯入到外部表格中填充的檔案。很顯然，Oracle希望將資料保留在資料庫內部進行處理。

首先，我們來看一下該文字檔的大小及記錄。

[[email protected] ~]$ du -sm P_20150626010000_2002371.0003479598 274    P_20150626010000_2002371.0003479598[[email protected] ~]$ 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.26SQL> INSERT /*+ APPEND */ INTO test select * from emp_load;2899265 rows created.Elapsed: 00:01:00.29SQL> 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 parallelElapsed: 00:00:00.10SQL> commit;Commit complete.Elapsed: 00:00:00.07SQL> select * from test where rownum<=100;

在這裡，為了節省時間，我用了直接路徑插入，可以看出，插入近300萬資料，只用了1分左右的時間，考慮到我虛擬機器上的資料庫，只給它分配了300M的記憶體，載入的效率還是相當可觀的。

SQL> show parameter memoryNAME                     TYPE     VALUE------------------------------------ ----------- ------------------------------hi_shared_memory_address         integer     0memory_max_target             big integer 300Mmemory_target                 big integer 300Mshared_memory_address             integer     0

注意：在SQL*PLUS中，number欄位的輸出預設為10，這樣會導致對於937116510102250300這樣的數值，可能會顯示為9.3712E+17，在這裡，可通過set numwidth 18來顯示完整的number欄位的值。

 

如何利用Oracle外部表格匯入文字檔的資料