[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。
--这样有可能出现一种情况，如果分区很多，插入很少的数据，占用非常大的磁盘空间。
时间： 2024-11-14 11:14:49