[20130407]分区的段大小2.txt

参看链接：http://hemantoracledba.blogspot.com/2013/03/segment-size-of-partition-11202-and.html

自己重复它的测试，仅仅使用表空间名字与他不同吧了。

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

继续测试，看看不使用段延迟建立的情况如何呢？


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

--使用 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                                1024       8192

A_PARTITIONED_TABLE  P_ID_200                             1024       8192

A_PARTITIONED_TABLE  P_ID_100                             1024       8192

--可以发现问题依旧，在没有指定相关参数的情况下，建立的分区即使没有数据，占用也是8M。

drop table a_partitioned_table purge ;

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 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645）tablespace TEST,

partition P_ID_200 values less than ('201') SEGMENT CREATION IMMEDIATE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645）tablespace TEST,

partition P_MAX values less than (MAXVALUE) SEGMENT CREATION IMMEDIATE STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645）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。

2.插入数据看看:

SQL> insert into a_partitioned_table values (150,'HundredFifty');

1 row created.

SQL> commit ;

Commit complete.

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

3.拆分分区看看？

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_100 at ('51')  into (partition P_ID_50, partition P_ID_100);

Table altered.

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_200 at ('151') into (partition P_ID_150, partition P_ID_200);

Table altered.

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_ID_50                                 8         64

A_PARTITIONED_TABLE  P_MAX                                   8         64

A_PARTITIONED_TABLE  P_ID_200                                8         64

A_PARTITIONED_TABLE  P_ID_100                                8         64

A_PARTITIONED_TABLE  P_ID_150                                8         64

SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE',user) from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE',USER)

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

  CREATE TABLE "SCOTT"."A_PARTITIONED_TABLE"

   (    "ID" NUMBER NOT NULL ENABLE,

        "DATA_COLUMN" VARCHAR2(20)

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

  PARTITION BY RANGE ("ID")

 (PARTITION "P_ID_50"  VALUES LESS THAN ('51') SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

 PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

 PARTITION "P_ID_150"  VALUES LESS THAN ('151') SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" )

--这样就没有问题，实际上这个问题仅仅存在分区很多，而各个分区数据很少的情况下要注意。