INITIAL_EXTENT

1、作用

Specify the size of the first extent of the object. Oracle allocates space for this extent when you create the schema object.

When you create a cluster, index, materialized view, materialized view log, rollback segment, table, LOB, varray, nested table, or partition, you can specify values for the storage parameters for the segments allocated to these objects. If you omit any storage parameter, then Oracle uses the value of that parameter specified for the tablespace in which the object resides. If no value was specified for the tablespace, then the database uses default values.

2、设置INITIAL_EXTENT

在创建数据库对象或表空间时通过storage子句中的initial参数来设置INITIAL_EXTENT

2.1、为普通表设置INITIAL_EXTENT

将divisions表的INITIAL_EXTENT设置为8M

CREATE TABLE divisions (
     div_no     NUMBER(2), 
     div_name   VARCHAR2(14), 
     location   VARCHAR2(13) 
)  STORAGE  ( INITIAL 8M  );

2.2、为分区表的每个分区设置INITIAL_EXTENT

将test13表分区p1的INITIAL_EXTENT设置为8M,分区p2的INITIAL_EXTENT设置为18M。

create table test13 (id number, d varchar2(10))
partition  by list (id)
(
partition p1 values (1) storage ( initial 8m),
partition p2 values (2) storage ( initial 18m)
);

上述语句为表test13的两个分区p1,p2分别设置了不同的INITIAL_EXTENT。由于没有为该表指定了INITIAL SIZE，对于新增的分区Oracle将根据该分区所在表空间的属性来自动分配INITIAL_EXTENT;如果表空间extent的管理方式为AUTOALLOCATE, Oracle 11.2.0.1.0版本默认为64k, 11.2.0.2.0以后版本默认为8M.

增加一个新分区p3,且不指定INITIAL SIZE.

alter table test13 add partition p3 values(3) ;

通过user_segments视图查看p1,p2,p3分区的INITIAL_EXTENT大小

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	BYTES	INITIAL_EXTENT
TEST13	P1	TABLE PARTITION	8388608	8388608
TEST13	P2	TABLE PARTITION	18874368	18874368
TEST13	P3	TABLE PARTITION	65536	65536

2.3、为分区表的每个分区设置相同的INITIAL_EXTENT

通过设置表级的INITIAL SIZE，来为不同分区指定，相同大小的INITIAL_EXTENT。

create table test14 (id number, d varchar2(10))
partition  by list (id)
(
partition p1 values (1),
partition p2 values (2)
) storage ( initial 10m);

上述语句为表test14的两个分区p1,p2设置了相同的INITIAL_EXTENT。由于为该表指定了INITIAL SIZE，对于新增的分区Oracle将根据INITIAL SIZE来分配INITIAL_EXTENT。

可以在user_part_tables视图中查看该分区表默认的initial szie。

select pt.table_name, pt.partitioning_type, pt.def_initial_extent
  from user_part_tables pt
 where pt.table_name = 'TEST14'

TABLE_NAME	PARTITIONING_TYPE	DEF_INITIAL_EXTENT
TEST14	LIST	1280

此处的DEF_INITIAL_EXTENT是数据块数量，INITIAL_EXTENT = DEF_INITIAL_EXTENT *  BLOCK_SIZE = 1280 * 8k = 10M

增加一个新分区p3,且不指定INITIAL SIZE。

alter table test14 add partition p3 values(3) ;

通过user_segments视图查看p1,p2,p3分区的INITIAL_EXTENT大小

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	BYTES	INITIAL_EXTENT
TEST14	P1	TABLE PARTITION	10485760	10485760
TEST14	P2	TABLE PARTITION	10485760	10485760
TEST14	P3	TABLE PARTITION	10485760	10485760

3、INITIAL的规则

3.1、本地管理表空间

In locally managed tablespaces, Oracle uses the value of INITIAL, in conjunction with the type of local management—AUTOALLOCATE or UNIFORM—and the values of MINEXTENTS, NEXT and PCTINCREASE, to determine the initial size of the segment.

• With AUTOALLOCATE extent management, Oracle uses the INITIAL setting to optimize the number of extents allocated. Extents of 64K, 1M, 8M, and 64M can be allocated. During segment creation, the system chooses the greatest of these four sizes that is equal to or smaller than INITIAL, and allocates as many extents of that size as are needed to reach the INITIAL setting. For example, if you set INITIAL to 4M, then the database creates four 1M extents.
• For UNIFORM extent management, the number of extents is determined from initial segment size and the uniform extent size specified at tablespace creation time. For example, in a uniform locally managed tablespace with 1M extents, if you specify an INITIAL value of 5M, then Oracle creates five 1M extents.

Consider this comparison: With AUTOALLOCATE, if you set INITAL to 72K, then the initial segment size will be 128K (greater than INITIAL). The database cannot allocate an extent smaller than 64K, so it must allocate two 64K extents. If you set INITIAL to 72K with a UNIFORM extent size of 24K, then the database will allocate three 24K extents to equal 72K.

3.2、数据字典管理表空间

In dictionary managed tablespaces, the default initial extent size is 5 blocks, and all subsequent extents are rounded to 5 blocks. If MINIMUM EXTENT was specified at tablespace creation time, then the extent sizes are rounded to the value of MINIMUM EXTENT.

4、查看INITIAL_EXTENT及分配的extents

4.1、查看INITIAL_EXTENT

select s.segment_name,
       s.partition_name,
       s.segment_type,
       s.bytes,
       s.initial_extent
  from user_segments s
 where SEGMENT_NAME in ('TEST13', 'DIVISIONS');

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	BYTES	INITIAL_EXTENT
DIVISIONS		TABLE	8388608	8388608
TEST13	P1	TABLE PARTITION	8388608	8388608
TEST13	P2	TABLE PARTITION	18874368	18874368

4.2、查看extents

select e.segment_name,
       e.partition_name,
       e.segment_type,
       e.extent_id,
       e.bytes
  from user_extents e
 where SEGMENT_NAME in ('TEST13', 'DIVISIONS');

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	EXTENT_ID	BYTES
DIVISIONS		TABLE	0	8388608
TEST13	P1	TABLE PARTITION	0	8388608
TEST13	P2	TABLE PARTITION	0	8388608
TEST13	P2	TABLE PARTITION	1	8388608
TEST13	P2	TABLE PARTITION	2	1048576
TEST13	P2	TABLE PARTITION	3	1048576

5、修改INITIAL_EXTENT

INITIAL参数的限制：You cannot specify in an ALTER statement。

修改INITIAL_EXTENT实际是利用表迁移的方法。在使用alter table ... move语句时，有以下几点需要注意：

• alter table move 省略了tablespace XXX, 表示用户移到自己默认的表空间,因此当前表空间的空闲空间至少要大于该表所占用的空间.
  
• alter table move过程中会导致索引失效，必须要考虑重新索引.
  
• alter table move过程中会产生锁，应该避免在业务高峰期操作.

5.1、修改普通表INITIAL_EXTENT

alter table divisions move storage ( initial 64k);

5.2、修改分区表已建分区的INITIAL_EXTENT

alter table test13 move partition p1 storage ( initial 64k);
alter table test13 move partition p2 storage ( initial 64k);

5.3、查看INITIAL_EXTENT及分配的extents

查询user_segments和user_extents,可以看出INITIAL_EXTENT及分配的extent都产生了变化.

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	BYTES	INITIAL_EXTENT
DIVISIONS		TABLE	65536	65536
TEST13	P1	TABLE PARTITION	65536	65536
TEST13	P2	TABLE PARTITION	65536	65536

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	EXTENT_ID	BYTES
DIVISIONS		TABLE	0	65536
TEST13	P1	TABLE PARTITION	0	65536
TEST13	P2	TABLE PARTITION	0	65536

5.4、修改分区表默认INITIAL_EXTENT

在2.3节中分区表test14默认的def_initial_extent为10m,可以通过以下语句将其修改为64k。

alter table test14 modify default attributes storage (initial 64k);

此时查看视图user_part_tables可以看到def_initial_extent已修改为64k = 8 * 8k

TABLE_NAME	PARTITIONING_TYPE	DEF_INITIAL_EXTENT
TEST14	LIST	8

该语句只对新建的分区有效，对已经创建的分区无影响。

为test14新建分区P4， 确认def_initial_extent已生效。

alter table test14 add partition p4 values(4);

查看视图user_segments确认新分区的INITIAL_EXTENT

SEGMENT_NAME	PARTITION_NAME	SEGMENT_TYPE	BYTES	INITIAL_EXTENT
TEST14	P1	TABLE PARTITION	10485760	10485760
TEST14	P2	TABLE PARTITION	10485760	10485760
TEST14	P3	TABLE PARTITION	10485760	10485760
TEST14	P4	TABLE PARTITION	65536	65536