oracle11g之create table存储参数initial和表空间autoallocate或uniform的关系

背景

  建表可以指定initial及next即分别指定初始区及再次分配区的大小，这些参数与区分配管理模式autoallocate及uniform又有何关系呢？
本文我们来回答这个问题。  

结论

  1,创建表时指定initial的优先级要高于所属表空间指定的autoallocate或者uniform的区管理模式


  2,表指定的initial时，表的初始区分配大小，会分为2个情况
      基于autoallocate区管理模式时，表的初始区分配大小，因为ORACLE分配的区大小有64K，1M，8M,64M;从官方文档可知，会从这4个区中选最接近initial指定的值的最大值，刚好就是1M的区
      采用四舍五入计算区的大小及个数


      基于uniform区管理模式，表的初始区如何分配，取决于建表时指定的initial及创建表空间时指定的uniform区大小，所以这里表的初始区大小为3m,可见是由2个因素决定初始区如何分配；
      可见虽是由2个因素确定表的初始区分配大小，主要因素仍是所属表空间uniform大小，且采用四舍五入方式分配区个数及大小

测试

1,数据库版本
SQL> conn scott/system
Connected.
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


2,先看下基于本地管理表空间的autoallocate区管理模式下，创建表时指定initial，会如何表现？
SQL> select default_tablespace from user_users;


DEFAULT_TABLESPACE
------------------------------------------------------------
USERS


SQL> select tablespace_name,initial_extent,next_extent,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name='USERS';


TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT    ALLOCATION_TYPE    SEGMENT_SPAC
------------------------------ -------------- ----------- -------------------- ------------------ ------------
USERS                                   65536             LOCAL                SYSTEM             AUTO


SQL> create table t_initial_1(a int,b int) storage(initial 5m);


Table created.


SQL> select segment_name,extent_id,blocks,bytes from user_extents where segment_name='T_INITIAL_1';


no rows selected


SQL> insert into t_initial_1 values(1,1);


1 row created.


SQL> commit;


Commit complete.


可见initial指定初始分配的共计区大小
SQL> select segment_name,extent_id,blocks,bytes/1024/1024 initial_mb from user_extents where segment_name='T_INITIAL_1';


SEGMENT_NAME                    EXTENT_ID     BLOCKS INITIAL_MB
------------------------------ ---------- ---------- ----------
T_INITIAL_1                             0        128          1
T_INITIAL_1                             1        128          1
T_INITIAL_1                             2        128          1
T_INITIAL_1                             3        128          1
T_INITIAL_1                             4        128          1


但为何会分配共计5个1m的区呢，因为对于autoallocate的区管理模式，ORACLE分配的区大小有64K，1M，8M,64M;从官方文档可知，会从这4个区中选最接近initial指定的值的最大值，刚好就是1M的区，
所以会分配5个区,下面的测试正好验证这一点


SQL> drop table t_initial_1 purge;


Table dropped.


SQL> create table t_initial_1(a int,b int) storage(initial 72k);


Table created.


SQL> insert into t_initial_1 values(1,1);


1 row created.


SQL> commit;


Commit complete.






SQL> select segment_name,extent_id,blocks,bytes/1024 initial_kb from user_extents where segment_name='T_INITIAL_1';


SEGMENT_NAME                    EXTENT_ID     BLOCKS INITIAL_KB
------------------------------ ---------- ---------- ----------
T_INITIAL_1                             0          8         64
T_INITIAL_1                             1          8         64




SQL> drop table t_initial_1 purge;


Table dropped.


SQL> create table t_initial_1(a int,b int) storage(initial 9m);


Table created.


SQL> insert into t_initial_1 values(1,1);


1 row created.


SQL> commit;


Commit complete.


SQL> select segment_name,extent_id,blocks,bytes/1024/1024 initial_mb from user_extents where segment_name='T_INITIAL_1';


SEGMENT_NAME                    EXTENT_ID     BLOCKS INITIAL_MB
------------------------------ ---------- ---------- ----------
T_INITIAL_1                             0       1024          8
T_INITIAL_1                             1        128          1




3，再看下基于本地管理表空间的iniform区管理模式下，创建表时指定initial，会如何表现？


可见基于uniform区管理模式的初始区分配大小为3m
SQL> select tablespace_name,initial_extent,next_extent,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name='TBS_UNIFORM';


TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT    ALLOCATION_TYPE    SEGMENT_SPAC
------------------------------ -------------- ----------- -------------------- ------------------ ------------
TBS_UNIFORM                           3145728     3145728 LOCAL                UNIFORM            AUTO


SQL> select 3145728/1024/1024 initial_mb from dual;


INITIAL_MB
----------
         3


对于uniform区管理模式，表的初始区如何分配，取决于建表时指定的initial及创建表空间时指定的uniform区大小，所以这里表的初始区大小为3m,可见是由2个因素决定初始区如何分配
SQL> create table t_initial_2(a int,b int) tablespace tbs_uniform storage(initial 72k);


Table created.




SQL> insert into t_initial_2 values(1,1);


1 row created.


SQL> commit;


Commit complete.


SQL> select segment_name,extent_id,blocks,bytes/1024/1024 initial_mb from user_extents where segment_name='T_INITIAL_2';


SEGMENT_NAME                    EXTENT_ID     BLOCKS INITIAL_MB
------------------------------ ---------- ---------- ----------
T_INITIAL_2                             0        384          3


可见虽是由2个因素确定表的初始区分配大小，主要因素仍是所属表空间uniform大小，且采用四舍五入方式分配区个数及大小
SQL> drop table t_initial_2 purge;


Table dropped.


SQL> create table t_initial_2(a int,b int) tablespace tbs_uniform storage(initial 4m);


Table created.


SQL> insert into t_initial_2 values(1,1);


1 row created.


SQL> commit;


Commit complete.


SQL> select segment_name,extent_id,blocks,bytes/1024/1024 initial_mb from user_extents where segment_name='T_INITIAL_2';


SEGMENT_NAME                    EXTENT_ID     BLOCKS INITIAL_MB
------------------------------ ---------- ---------- ----------
T_INITIAL_2                             0        384          3
T_INITIAL_2                             1        384          3




SQL> drop table t_initial_2 purge;


Table dropped.


SQL> create table t_initial_2(a int,b int) tablespace tbs_uniform storage(initial 20m);


Table created.


SQL> insert into t_initial_2 values(1,1);


1 row created.


SQL> commit;


Commit complete.


SQL> select segment_name,extent_id,blocks,bytes/1024/1024 initial_mb from user_extents where segment_name='T_INITIAL_2';


SEGMENT_NAME                    EXTENT_ID     BLOCKS INITIAL_MB
------------------------------ ---------- ---------- ----------
T_INITIAL_2                             0        384          3
T_INITIAL_2                             1        384          3
T_INITIAL_2                             2        384          3
T_INITIAL_2                             3        384          3
T_INITIAL_2                             4        384          3
T_INITIAL_2                             5        384          3
T_INITIAL_2                             6        384          3


7 rows selected.

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院     

河北廊坊新奥集团公司

 项目经验:

中国电信3G项目AAA系统数据库部署及优化
      中国联通4G数据库性能分析与优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

国家电网上海灾备项目4 node rac+adg 
       贵州移动crm及客服数据库性能优化项目
       贵州移动crm及客服务数据库sql审核项目
       深圳穆迪软件有限公司数据库性能优化项目

联系方式：

手机：18201115468

qq   :   305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900    

itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/