[20130402]分区的段大小.txt

[20130402]分区的段大小.txt

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

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

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

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') tablespace TEST,

partition P_ID_200 values less than ('201') tablespace TEST,

partition P_MAX values less than (MAXVALUE) tablespace TEST

);

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_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

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

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

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

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" )

--因为段延迟建立的特性，并没有分配相应的段。

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

no rows selected

SQL> column table_name format a30

SQL> select table_name, partition_name from user_tab_partitions where table_name='A_PARTITIONED_TABLE';

TABLE_NAME                     PARTITION_NAME

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

A_PARTITIONED_TABLE            P_ID_100

A_PARTITIONED_TABLE            P_ID_200

A_PARTITIONED_TABLE            P_MAX

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_ID_200                             1024       8192

--可以发现bytes/1024 =8192,也就是8M。

select segment_name, partition_name, extent_id, file_id, block_id, blocks

from dba_extents

 where tablespace_name = 'TEST';

SEGMENT_NAME         PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS

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

A_PARTITIONED_TABLE  P_ID_200                                0          8        256       1024

--分区P_ID_200占用1024块，也就是8M。

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_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  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 8388608 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 DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" )

    Partition P_ID_200变为了SEGMENT CREATION IMMEDIATE，注意存储的分配形式：STORAGE(INITIAL 8388608 NEXT 1048576

MINEXTENTS 1 MAXEXTENTS 2147483645)。

如果拆分分区呢？

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);

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 DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "TEST" ,

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

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  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 8388608 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 8388608 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 DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  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_ID_150                             1024       8192

A_PARTITIONED_TABLE  P_ID_200                             1024       8192

--可以发现原来有数据的分区拆分后继承了P_ID_200的分配特性。占用8M。
--这样有可能出现一种情况，如果分区很多，插入很少的数据，占用非常大的磁盘空间。