[20130816]分区段大小3.txt

http://space.itpub.net/267265/viewspace-757670
http://space.itpub.net/267265/viewspace-757871

前面的建立分区表的例子，说明如果存在多个分区，如果分区很多，每个分区数据很少的情况下，会导致磁盘空间的大量浪费，
昨天看惜分飞的blog，链接如下：
http://www.xifenfei.com/4620.html
分区默认segment大小变化(64k—>8M)

里面提高一个参数可以控制这种行为：_partition_large_extents。

SQL> @hide _partition_large_extents

old  10:  and lower(a.ksppinm) like lower('%&1%')

new  10:  and lower(a.ksppinm) like lower('%_partition_large_extents%')

NAME                            DESCRIPTION                                              DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE

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

_index_partition_large_extents  Enables large extent allocation for partitioned indices  TRUE           FALSE          FALSE

_partition_large_extents        Enables large extent allocation for partitioned tables   TRUE           TRUE           TRUE

--缺省这个参数_partition_large_extents=true.这样一旦空间分配，马上使用8M。

自己再做一些测试：

1.测试环境:

SQL> @ver

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

select extent_management, allocation_type, segment_space_management,

initial_extent, next_extent, pct_increase

from dba_tablespaces where tablespace_name = 'TEST';

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE

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

LOCAL      SYSTEM    AUTO            65536

SQL> alter session set "_partition_large_extents"=false;


--使用 SEGMENT CREATION IMMEDIATE .
create table a_partitioned_table
(id   number not null,
data_column varchar2(20)
)
partition by range (id)
(
partition P_ID_100 values less than ('101') SEGMENT CREATION IMMEDIATE tablespace TEST,
partition P_ID_200 values less than ('201') SEGMENT CREATION IMMEDIATE tablespace TEST,
partition P_MAX values less than (MAXVALUE) SEGMENT CREATION IMMEDIATE tablespace TEST
);

SQL> select segment_name, partition_name, blocks, bytes/1024 from dba_segments where tablespace_name = 'TEST';

SEGMENT_NAME         PARTITION_NAME                     BLOCKS BYTES/1024

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

A_PARTITIONED_TABLE  P_MAX                                   8         64

A_PARTITIONED_TABLE  P_ID_200                                8         64

A_PARTITIONED_TABLE  P_ID_100                                8         64

--可以发现建立的分区即使没有数据，占用也是64k，而不是原来的8M。
--以此文作为必要的补充，仅仅要注意存在分区很多，而各个分区数据很少的情况下要注意，避免磁盘空间的浪费。