多BLOCKSIZE对分区表的影响

多BLOCKSIZE对分区表的影响

引自：http://space.itpub.net/4227/viewspace-628191

看文档的时候提到了多个BLOCKSIZE对分区的影响，觉得比较有意思，于是测试了一下。

 

 

显然Oracle是不允许一个表的多个分区所在的表空间所处的表空间的BLOCKSIZE不同：

SQL> SHOW PARAMETER BLOCK_SIZE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_size                        integer     16384

SQL> SELECT NAME FROM V$DATAFILE;

NAME

--------------------------------------------------------------------

/data/oradata/test112/system01.dbf

/data/oradata/test112/sysaux01.dbf

/data/oradata/test112/undotbs01.dbf

/data/oradata/test112/users01.dbf

/data/oradata/test112/yangtk01.dbf

/data/oradata/test112/test01.dbf

已选择6行。

SQL> SHOW PARAMETER DB_8K

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_8k_cache_size                     big integer 0

SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE =64MSCOPE = MEMORY;

系统已更改。

SQL> CREATE TABLESPACE TBS_8K

  2  DATAFILE '/data/oradata/test112/tbs_8k01.dbf'

  3  SIZE512M

  4  BLOCKSIZE 8K;

表空间已创建。

SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE  

  2  FROM DBA_TABLESPACES;

TABLESPACE_NAME                BLOCK_SIZE

------------------------------ ----------

SYSTEM                              16384

SYSAUX                              16384

UNDOTBS1                            16384

TEMP                                16384

USERS                               16384

YANGTK                              16384

TEST                                16384

TBS_8K                               8192

已选择8行。

SQL> CREATE TABLE T_PART

  2  (ID NUMBER,

  3  NAME VARCHAR2(30),

  4  CREATE_DATE DATE)

  5  PARTITION BY RANGE (ID)

  6  (PARTITION P1 VALUES LESS THAN (100),

  7  PARTITION P2 VALUES LESS THAN (200) TABLESPACE TBS_8K);

PARTITION P2 VALUES LESS THAN (200) TABLESPACE TBS_8K)

                                               *

第7行出现错误:

ORA-14519:与table 的表空间块大小存在冲突:表空间TBS_8K的块大小8192 [partition specification]

与以前指定/隐含的表空间YANGTK的块大小16384 [user default tablespace assigned to partition]发生冲突

但是允许索引和表的BLOCKSIZE不一致：

SQL> CREATE TABLE T_PART

  2  (ID NUMBER,

  3  NAME VARCHAR2(30),

  4  CREATE_DATE DATE)

  5  PARTITION BY RANGE (ID)

  6  (PARTITION P1 VALUES LESS THAN (100),

  7  PARTITION P2 VALUES LESS THAN (200));

表已创建。

SQL> CREATE INDEX IND_T_PART_NAME ON T_PART (NAME) LOCAL

  2  (PARTITION P1 TABLESPACE TBS_8K, PARTITION P2 TABLESPACE TBS_8K);

索引已创建。

SQL> CREATE INDEX IND_T_PART_CDATE ON T_PART(CREATE_DATE) TABLESPACE TBS_8K;

索引已创建。

SQL> CREATE INDEX IND_T_PART_ID ON T_PART(ID) LOCAL;

索引已创建。

SQL> SELECT INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME

  2  FROM USER_IND_PARTITIONS

  3  WHERE INDEX_NAME LIKE 'IND_T_PART%';

INDEX_NAME                     PARTITION_NAME                 TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

IND_T_PART_ID                  P1                             YANGTK

IND_T_PART_ID                  P2                             YANGTK

IND_T_PART_NAME                P1                             TBS_8K

IND_T_PART_NAME                P2                             TBS_8K

索引的各个分区所在表空间要求BLOCKSIZE一致，但是不要求和表的分区BLOCKSIZE一致。表的不同索引可以存储在不同BLOCKSIZE的表空间上。

除了索引之外，表的LOB字段可以和表存放在不同的BLOCKSIZE的表空间中，同样的，分区表的LOB分区所在表空间的BLOCKSIZE可以和表分区所在表空间的BLOCKSIZE不同：

SQL> CREATE TABLE T_PART_LOB

  2  (ID NUMBER,

  3  NAME VARCHAR2(30),

  4  CREATE_DATE DATE,

  5  CONTENTS CLOB)

  6  LOB (CONTENTS) STORE AS CONTENTS_LOB

  7  PARTITION BY RANGE (ID)

  8  (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST

  9  LOB (CONTENTS) STORE AS P1 (TABLESPACE TBS_8K),

 10  PARTITION P2 VALUES LESS THAN (200)

 11  LOB (CONTENTS) STORE AS P2 (TABLESPACE TBS_8K));

表已创建。

当然，分区的表的LOB的各个分区必须存在在相同的BLOCKSIZE的表空间上，否则会报错：

SQL> CREATE TABLE T_PART_LOB_ERR

  2  (ID NUMBER,

  3  NAME VARCHAR2(30),

  4  CREATE_DATE DATE,

  5  CONTENTS CLOB)

  6  LOB (CONTENTS) STORE AS CONTENTS_LOB_ERR

  7  PARTITION BY RANGE (ID)

  8  (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST

  9  LOB (CONTENTS) STORE AS P1 (TABLESPACE TBS_8K),

 10  PARTITION P2 VALUES LESS THAN (200)

 11  LOB (CONTENTS) STORE AS P2 (TABLESPACE YANGTK));

LOB (CONTENTS) STORE AS P2 (TABLESPACE YANGTK))

                                       *

第11行出现错误:

ORA-14519:与LOB column CONTENTS的表空间块大小存在冲突:表空间YANGTK的块大小16384 [partition specification]

与以前指定/隐含的表空间TBS_8K的块大小8192 [partition specification]发生冲突

同样的限制条件也适用于索引组织表的OVERFLOW段：

SQL> CREATE TABLE T_IND_PART

  2  (ID NUMBER PRIMARY KEY,

  3  NAME VARCHAR2(30),

  4  CREATE_DATE DATE)

  5  ORGANIZATION INDEX

  6  INCLUDING NAME

  7  OVERFLOW

  8  PARTITION BY RANGE (ID)

  9  (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST

 10  OVERFLOW TABLESPACE TBS_8K,

 11  PARTITION P2 VALUES LESS THAN (200)

 12  OVERFLOW TABLESPACE TBS_8K);

表已创建。

同样，不同的OVERFLOW分区所在表空间的BLOCKSIZE必须相同：

 SQL> CREATE TABLE T_IND_PART_ERR

  2  (ID NUMBER PRIMARY KEY,

  3  NAME VARCHAR2(30),

  4  CREATE_DATE DATE)

  5  ORGANIZATION INDEX

  6  INCLUDING NAME

  7  OVERFLOW

  8  PARTITION BY RANGE (ID)

  9  (PARTITION P1 VALUES LESS THAN (100) TABLESPACE TEST

 10  OVERFLOW TABLESPACE TBS_8K,

 11  PARTITION P2 VALUES LESS THAN (200)

 12  OVERFLOW TABLESPACE YANGTK);

OVERFLOW TABLESPACE YANGTK)

                    *

第12行出现错误:

ORA-14519:与IOT overflow 的表空间块大小存在冲突:表空间YANGTK的块大小16384 [partition specification]

与以前指定/隐含的表空间TBS_8K的块大小8192 [partition specification]发生冲突

基本上来说，OVERFLOW段和LOB段允许和表或表分区的BLOCKSIZE不一致，而各个分区，无论是表分区、索引分区、OVERFLOW分区还是LOB分区都必须保持分区级的一致。

允许LOB和OVER段与表的BLOCKSIZE不一致是有一定意义的，比如在一个BLOCKSIZE为2K的数据库中，如果LOB段的大小也是2K，对于LOB的存储容量和效率都会产生不利的影响。

而允许不同分区的BLOCKSIZE不一致其实也是有一定意义的，这样有利于不同数据库之间进行表空间迁移和EXCHANGE PARTITION操作，不过现在Oracle还不允许这种情况出现。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/276487/viewspace-628582/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/276487/viewspace-628582/