TRUNCATE模式SQLLDR導致SECUREFILE的LOB空間不斷增長

yangtingkun發表於2011-08-22

測試LOBSECUREFILE儲存方式時發現,如果利用SQLLDRTRUNCATE方式匯入資料,隨著測試次數的增加,LOB物件佔用的空間也會逐步增加。

 

 

建立表的指令碼很簡單:

create table t_load_4m_sf (id number, full_name varchar2(100),
create_date date, contents blob, constraint pk_t_load_4m_sf primary key(id))
lob (contents) store as securefile;

用於載入LOB資料的控制檔案如下:

[oracle@dbserver1 sqlldr]$ more sqlldr_4M_sf.ctl
LOAD DATA
INFILE 'filename.dat'
INTO TABLE T_LOAD_4M_SF
TRUNCATE
FIELDS TERMINATED BY ','
(ID CHAR(255),
FULL_NAME CHAR(255),
CREATE_DATE SYSDATE,
CONTENTS LOBFILE(FULL_NAME) TERMINATED BY EOF)

資料檔案格式如下:

[oracle@dbserver1 sqlldr]$ more filename.dat
1,/home/oracle/2M/IMG_5015.JPG
2,/home/oracle/2M/IMG_5016.JPG
3,/home/oracle/2M/IMG_5017.JPG
4,/home/oracle/2M/IMG_5018.JPG
5,/home/oracle/2M/IMG_5022.JPG
6,/home/oracle/2M/IMG_5023.JPG
7,/home/oracle/2M/IMG_5025.JPG
.
.
.
663,/home/oracle/2M/DSC00142.JPG
664,/home/oracle/2M/DSC00143.JPG

匯入命令如下:

[oracle@dbserver1 sqlldr]$ sqlldr enmotest/oracle control=sqlldr_4M_sf.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Mon Aug 8 17:32:54 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
Commit point reached - logical record count 256
Commit point reached - logical record count 320
Commit point reached - logical record count 384
Commit point reached - logical record count 448
Commit point reached - logical record count 512
Commit point reached - logical record count 576
Commit point reached - logical record count 640
Commit point reached - logical record count 664

第一次執行匯入,檢查表的空間佔用:

SQL> select table_name, a.segment_name, bytes/1024/1024 from user_segments a, user_lobs b where b.segment_name = a.segment_name  order by 1;

TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$            1220.1875

其中T_LOAD_4MLOB沒有采用SECUREFILE方式儲存的普通表。這個表每次執行TRUNCATE方式的SQLLDR載入後,空間並不會發生變化,但是T_LOAD_4M_SF隨著執行SQLLDR次數的增加,空間佔用的情況分別變為:

SQL> select table_name, a.segment_name, bytes/1024/1024 from user_segments a, user_lobs b where b.segment_name = a.segment_name  order by 1;

TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$            1348.1875

SQL> select table_name, a.segment_name, bytes/1024/1024 from user_segments a, user_lobs b where b.segment_name = a.segment_name  order by 1;

TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$            1540.1875

SQL> select table_name, a.segment_name, bytes/1024/1024 from user_segments a, user_lobs b where b.segment_name = a.segment_name  order by 1;

TABLE_NAME                     SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ------------------------------ ---------------
T_LOAD_4M                      SYS_LOB0000062585C00004$$                 1088
T_LOAD_4M_SF                   SYS_LOB0000062641C00004$$             1732.125

除了第一次載入空間增長了128M外,以後每次載入都會導致表空間增長192M。而同樣的操作並不會導致BASICFILE方式的LOB表。

導致這個現象的原因除了SECUREFILE儲存方式外,也與SQLLDRTRUNCATE匯入方式有關,SQLLDRTRUNCATE只是為了清空資料,由於隨後還要執行匯入,因此沒有必要回收分配的空間,因此這個TRUNCATE語句會保留現有的空間,在加上SECUREFILE的特殊性,導致了每次載入LOB都使得表的空間不斷的增長。

而如果嘗試直接執行TRUNCATE TABLE語句,則會使得表的高水位線被清空,表的大小恢復到0。雖然這裡存在一定的空間洩漏,但是對於實際生產環境中,這種TRUNCATESQLLDR方式應該極為罕見,因此並不會導致什麼危害。

 

 

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

相關文章