匯入一張18億條300G資料檔案的表經驗

e71hao發表於2017-08-28
1.問題：需要匯入一張18億資料的300G大的資料檔案

2. 客戶給了一個用imp匯出的資料檔案。包含了一個表xx。這個表的結構如下：

點選(此處)摺疊或開啟

  1. CREATE TABLE "username"."xx"
  2.    (    "DTLCARDNO" CHAR(16) NOT NULL ENABLE,
  3.     "DTLCITY" NUMBER(4,0),
  4.     "DTLCDCNT" NUMBER(6,0) NOT NULL ENABLE,
  5.     "DTLTXNCODE" NUMBER(4,0) NOT NULL ENABLE,
  6.     "DTLINNTYPE" NUMBER(4,0),
  7.     "DTLPOSID" VARCHAR2(12),
  8.     "DTLSAMID" VARCHAR2(16),
  9.     "DTLPOSSEQ" NUMBER(10,0),
  10.     "DTLDATE" NUMBER(8,0),
  11.     "DTLTIME" NUMBER(6,0) NOT NULL ENABLE,
  12.     "DTLSETTDATE" NUMBER(8,0),
  13.     "DTLCENSEQ" NUMBER(10,0),
  14.     "DTLAMT" NUMBER(9,0) NOT NULL ENABLE,
  15.     "DTLSLAMT" NUMBER(9,0),
  16.     "DTLBEFBAL" NUMBER(9,0) NOT NULL ENABLE,
  17.     "DTLAFTBAL" NUMBER(9,0),
  18.     "DTLSTATID" NUMBER(9,0),
  19.     "DTLERRCODE" NUMBER(6,0),
  20.     "DTLINNERR" NUMBER(6,0),
  21.     "DTLRSVD" VARCHAR2(10),
  22.     "DTLPKGID" NUMBER(10,0),
  23.     "DTLUNITID" NUMBER(8,0),
  24.     "DTLCRDTYPE" NUMBER(4,0),
  25.     "DTLTAC" CHAR(8),
  26.     "PARTFLAG" NUMBER(3,0) NOT NULL ENABLE
  27.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  28.   STORAGE(
  29.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  30.   TABLESPACE "CRDDTL01_TS"
  31.   PARTITION BY RANGE ("PARTFLAG")
  32.  (PARTITION "P_JY001" VALUES LESS THAN (1)
  33.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  34.   STORAGE(INITIAL 797966336 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  35.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  36.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  37.  PARTITION "P_JY002" VALUES LESS THAN (2)
  38.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  39.   STORAGE(INITIAL 751828992 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  40.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  41.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  42.  PARTITION "P_JY003" VALUES LESS THAN (3)
  43.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  44.   STORAGE(INITIAL 829423616 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  45.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  46.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  47.  PARTITION "P_JY004" VALUES LESS THAN (4)
  48.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  49.   STORAGE(INITIAL 886046720 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  50.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  51.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  52.  PARTITION "P_JY005" VALUES LESS THAN (5)
  53.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  54.   STORAGE(INITIAL 901775360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  55.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  56.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  57.  PARTITION "P_JY006" VALUES LESS THAN (6)
  58.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  59.   STORAGE(INITIAL 826277888 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  60.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  61.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  62.  PARTITION "P_JY007" VALUES LESS THAN (7)
  63.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  64.   STORAGE(INITIAL 803209216 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  65.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  66.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  67.  PARTITION "P_JY008" VALUES LESS THAN (8)
  68.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  69.   STORAGE(INITIAL 961544192 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  70.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  71.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  72.  PARTITION "P_JY009" VALUES LESS THAN (9)
  73.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  74.   STORAGE(INITIAL 995098624 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  75.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  76.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS ,
  77.  PARTITION "P_JY010" VALUES LESS THAN (10)
  78.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  79.   STORAGE(INITIAL 972029952 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  80.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  81.   TABLESPACE "CRDDTL01_TS" NOCOMPRESS , 
  82. 。。。。。。。。。。。。。。
  83. 類似的有360個分割槽


可以看到是個分割槽表。
匯入語句

點選(此處)摺疊或開啟

  1. echo %time% ;
  2. imp system/manager@orcl file=E:\yikatong\tlcarddtltb.dmp fromuser=u1 touser=u1 ignore=y log=E:\yikatong\tlcarddtltb.dmp.log indexes=N RECORDLENGTH=65535 buffer=502400000 commit=n feedback=10000000
  3. echo %time% ;
要求1千萬行響應一次，提交為N，不插入索引。buffer設定為500M

3.開始匯入。因為客戶也沒有提供資料檔案的大小，只提供了需要新建的表空間。我新建瞭如下可以自增的表空間。
create tablespace crddtl01_ts datafile 'D:/oracle/tablespace/crddtl01_ts.dbf' size 8024m autoextend on next 1024m autoallocate;

每次自增1024M。考慮到資料比較大，每次自增太小，花費時間小。接著開始匯入了。等待了幾個小時以後，報錯：
01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE
.剛開始有點蒙。我建立表空間都是自增長的。難道不能自增長。查詢下可以增長的。看看錶空間大小32G。原來oracle普通表空間檔案最大為32G。然後開始給每個表空間增加4個檔案。sql如下：

點選(此處)摺疊或開啟

  1. create tablespace crddtl01_ts datafile 'D:/oracle/tablespace/crddtl01_ts.dbf' size 8024m autoextend on next 1024m autoallocate;
  2. create tablespace crddtlidx01_ts datafile 'd:/oracle/tablespace/crddtlidx01_ts.dbf' size 5024m autoextend on next 1024m autoallocate;
  3. alter tablespace crddtl01_ts add datafile 'D:/oracle/tablespace/crddtl01a_ts.dbf' size 1024m autoextend on next 1024m ;
  4. alter tablespace crddtl01_ts add datafile 'D:/oracle/tablespace/crddtl01b_ts.dbf' size 1024m autoextend on next 1024m ;
  5. create tablespace crddtl02_ts datafile 'E:/oracle_data/crddtl02_ts.dbf' size 8024m autoextend on next 1024m autoallocate;

  7. alter tablespace crddtl02_ts add datafile 'E:/oracle_data/crddtl02a_ts.dbf' size 1024m autoextend on next 1024m ;
  8. alter tablespace crddtl02_ts add datafile 'E:/oracle_data/crddtl02b_ts.dbf' size 1024m autoextend on next 1024m ;

  10. create tablespace crddtlidx02_ts datafile 'E:/oracle_data/crddtlidx02_ts.dbf' size 5024m autoextend on next 1024m autoallocate;

  12. create tablespace crddtl03_ts datafile 'F:/oracleData/crddtl03_ts.dbf' size 8024m autoextend on next 1024m autoallocate;
  13. alter tablespace crddtl03_ts add datafile 'F:/oracleData/crddtl03a_ts.dbf' size 1024m autoextend on next 1024m;
  14. alter tablespace crddtl03_ts add datafile 'F:/oracleData/crddtl03b_ts.dbf' size 1024m autoextend on next 1024m;

  16. create tablespace crddtlidx03_ts datafile 'F:/oracleData/crddtlidx03_ts.dbf' size 5024m autoextend on next 1024m autoallocate;

  18. create tablespace crddtl04_ts datafile 'D:/oracle/tablespace/crddtl04_ts.dbf' size 8024m autoextend on next 1024m autoallocate;
  19. alter tablespace crddtl04_ts add datafile 'D:/oracle/tablespace/crddtl04a_ts.dbf' size 1024m autoextend on next 1024m ;
  20. alter tablespace crddtl04_ts add datafile 'D:/oracle/tablespace/crddtl04b_ts.dbf' size 1024m autoextend on next 1024m ;
於是接下來就開始了漫長的等待。
4.怎麼知道匯入了多少資料？匯入進展到什麼情況了呢？或者說匯入程式有沒有卡住，僵死呢？
第一看feedback.我在匯入程式設定了引數feedback=10000000,每匯入1千萬資料，響應一個黑點。
第二可以看匯入日誌。每匯入完一個分割槽，它會在日誌插入一條記錄。
第三開啟資源管理器，我們看看imp程式，佔用的cpu，硬碟，網路，記憶體資源。

但是這個伺服器當時點什麼都很慢。我分配一個8G的檔案，花費了10幾分鐘。這個怎麼解釋呢？
5.經過漫長的等待，我花了2天7個小時，把這個18億資料匯入進去了。。
最後看看資料檔案大小：

點選(此處)摺疊或開啟

  1. 目錄                                   大小（M）tablespace
  2. D:\ORACLE\TABLESPACE\CRDDTL01A_TS.DBF  32767 CRDDTL01_TS
  3. D:\ORACLE\TABLESPACE\CRDDTL01_TS.DBF 32767 CRDDTL01_TS
  4. D:\ORACLE\TABLESPACE\CRDDTL01B_TS.DBF 32767 CRDDTL01_TS
  5. F:\ORACLEDATA\CRDDTL01D_TS.DBF 25600 CRDDTL01_TS
  6. E:\ORACLE_DATA\CRDDTL02C_TS.DBF 9216 CRDDTL02_TS
  7. E:\ORACLE_DATA\CRDDTL02D_TS.DBF 8192 CRDDTL02_TS
  8. E:\ORACLE_DATA\CRDDTL02B_TS.DBF 32767 CRDDTL02_TS
  9. E:\ORACLE_DATA\CRDDTL02_TS.DBF 32767 CRDDTL02_TS
  10. E:\ORACLE_DATA\CRDDTL02A_TS.DBF 30720 CRDDTL02_TS
  11. F:\ORACLEDATA\CRDDTL03D_TS.DBF 5120 CRDDTL03_TS
  12. F:\ORACLEDATA\CRDDTL03B_TS.DBF 28672 CRDDTL03_TS
  13. F:\ORACLEDATA\CRDDTL03_TS.DBF 32600 CRDDTL03_TS
  14. F:\ORACLEDATA\CRDDTL03A_TS.DBF 27648 CRDDTL03_TS
  15. F:\ORACLEDATA\CRDDTL03C_TS.DBF 5120 CRDDTL03_TS
  16. D:\ORACLE\TABLESPACE\CRDDTL04A_TS.DBF 28672 CRDDTL04_TS
  17. D:\ORACLE\TABLESPACE\CRDDTL04B_TS.DBF 32767 CRDDTL04_TS
  18. E:\ORACLE_DATA\CRDDTL04D_TS.DBF 7168 CRDDTL04_TS
  19. E:\ORACLE_DATA\CRDDTL04C_TS.DBF 7168 CRDDTL04_TS
  20. D:\ORACLE\TABLESPACE\CRDDTL04_TS.DBF 32767 CRDDTL04_TS
一個表空間有4個檔案，幾乎達到120G

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30393770/viewspace-2144252/，如需轉載，請註明出處，否則將追究法律責任。

相關文章