对于LOB字段而言,绝大部分情况都是存储在单独的段中,对于分区表而言,LOB段采用类似LOCAL索引的等同分区。
看一个简单的例子,如果建立分区表的时候不明确指定LOB分区信息:
SQL> SELECT * FROM TAB;
未选定行
SQL> SELECT DEFAULT_TABLESPACE
2 FROM USER_USERS;
DEFAULT_TABLESPACE
------------------------------
YANGTK
SQL> CREATE TABLE T_LOB
2 (ID NUMBER,
3 CREATE_DATE DATE,
4 CONTENTS CLOB)
5 LOB (CONTENTS) STORE AS CONTENTS_LOB
6 PARTITION BY RANGE (CREATE_DATE)
7 (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-1', 'YYYY-MM')) TABLESPACE TEST,
8 PARTITION P2 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM')) TABLESPACE USERS,
9 PARTITION P3 VALUES LESS THAN (TO_DATE('2011-1', 'YYYY-MM')));
表已创建。
SQL> SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME
2 FROM USER_TAB_PARTITIONS;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_LOB P3 YANGTK
T_LOB P1 TEST
T_LOB P2 USERS
SQL> SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
2 FROM USER_SEGMENTS;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------- -------------------- ------------------ ---------------
T_LOB P1 TABLE PARTITION TEST
T_LOB P2 TABLE PARTITION USERS
T_LOB P3 TABLE PARTITION YANGTK
SYS_IL0000086250C00003$$ SYS_IL_P49 INDEX PARTITION TEST
SYS_IL0000086250C00003$$ SYS_IL_P50 INDEX PARTITION USERS
SYS_IL0000086250C00003$$ SYS_IL_P51 INDEX PARTITION YANGTK
CONTENTS_LOB SYS_LOB_P46 LOB PARTITION TEST
CONTENTS_LOB SYS_LOB_P47 LOB PARTITION USERS
CONTENTS_LOB SYS_LOB_P48 LOB PARTITION YANGTK
已选择9行。
对于LOB分区的存储,可以通过LOB分区语句进行设置:
SQL> DROP TABLE T_LOB PURGE;
表已删除。
SQL> CREATE TABLE T_LOB
2 (ID NUMBER,
3 CREATE_DATE DATE,
4 CONTENTS CLOB)
5 LOB (CONTENTS) STORE AS CONTENTS_LOB
6 PARTITION BY RANGE (CREATE_DATE)
7 (PARTITION P1 VALUES LESS THAN (TO_DATE('2009-1', 'YYYY-MM')) TABLESPACE TEST
8 LOB (CONTENTS) STORE AS CP1 (TABLESPACE USERS),
9 PARTITION P2 VALUES LESS THAN (TO_DATE('2010-1', 'YYYY-MM'))
10 LOB (CONTENTS) STORE AS CP2 (TABLESPACE TEST),
11 PARTITION P3 VALUES LESS THAN (TO_DATE('2011-1', 'YYYY-MM')) TABLESPACE YANGTK);
表已创建。
SQL> SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME
2 FROM USER_SEGMENTS;
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------- -------------------- ------------------ ---------------
T_LOB P1 TABLE PARTITION TEST
T_LOB P2 TABLE PARTITION YANGTK
T_LOB P3 TABLE PARTITION YANGTK
SYS_IL0000086262C00003$$ SYS_IL_P53 INDEX PARTITION USERS
SYS_IL0000086262C00003$$ SYS_IL_P54 INDEX PARTITION TEST
SYS_IL0000086262C00003$$ SYS_IL_P55 INDEX PARTITION YANGTK
CONTENTS_LOB CP1 LOB PARTITION USERS
CONTENTS_LOB CP2 LOB PARTITION TEST
CONTENTS_LOB SYS_LOB_P52 LOB PARTITION YANGTK
已选择9行。
对于没有明确指定LOB分区存储表空间的分区,其默认表空间设置依赖对应的分区的表空间。而且可以看到,LOB索引的分区的表空间设置,完全依赖LOB分区设置,而和表分区的表空间设置没有关系。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-627688/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-627688/