Segment Size of a Partition (11.2.0.2 and above)

In 11.2.0.2, Oracle introduced a change to the default size of Partition Segment.
According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 [ID 1295484.1]",  the INITIAL Extent of a Partition is now 8MB.  Furthermore, Deferred Segment Creation also applies to Partitions.

Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless.  As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.

Here is a simple demo :

SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 9 16:45:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
ERROR:
ORA-28002: the password will expire within 7 days



Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> create tablespace PARTITION_TEST ;      

Tablespace created.

SQL> 
SQL> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name = 'PARTITION_TEST'
  5  /

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

SQL> 
SQL> l
  1  create table a_partitioned_table
  2  (id   number not null,
  3  data_column varchar2(20)
  4  )
  5  partition by range (id)
  6  (
  7  partition P_ID_100 values less than ('101') tablespace PARTITION_TEST,
  8  partition P_ID_200 values less than ('201') tablespace PARTITION_TEST,
  9  partition P_MAX values less than (MAXVALUE) tablespace PARTITION_TEST
 10* )
SQL> /

Table created.

SQL> 
SQL> set pages600
SQL> set long 32767
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."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 "PARTITION_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 "PARTITION_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 "PARTITION_TEST" )


SQL> 

Note how the two Partitions are created with "SEGMENT CREATION DEFERRED" and there is no Storage allocated to them yet. Let's look for the Segments and Extents.

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

no rows selected

SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select table_name, partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
A_PARTITIONED_TABLE            P_MAX
A_PARTITIONED_TABLE            P_ID_200
A_PARTITIONED_TABLE            P_ID_100

SQL> 

So, although the three Partitions exist, no Segments and Extents yet exist. Now, let's populate one Partition -- just inserting a single row will suffice
.

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

1 row created.

SQL> commit;

Commit complete.

SQL> 

Now, let's look for the Segment and Extent. Note that Partition P_ID_200 would have the row with ID=150.

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

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                                0          6        128       1024


SQL> 

So, Partition P_ID_200 now has an 8MB Segment with an Initial Extent of 8MB. Let's view the DDL for the Table.

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

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."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 "PARTITION_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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_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 "PARTITION_TEST" )


SQL> 

Note how Partitions P_ID_100 and P_MAX are unchanged but Partition P_ID_200 now has SEGMENT CREATION IMMEDIATE and STORAGE (INITIAL 8M NEXT 1M) !

What if I were to split the Partitions to create new Partitions ?

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

Table altered.

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

Table altered.

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

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."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 "PARTITION_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 "PARTITION_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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_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 "PARTITION_TEST" )


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

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192

A_PARTITIONED_TABLE
P_ID_150                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_150                                0          6        128       1024

A_PARTITIONED_TABLE
P_ID_200                                0          6       1152       1024


SQL> 

So, only the two Partitions that have been created out of P_ID_200 have been materialized as Segments and Extents while the two Partitions split out of P_ID_100 are still Segment-less (without Storage allocated).

So remember : the default behaviour of Segment creation for Partitions has been changed since 11.2.0.2
Having said that, the note that I mentinoed at the beginning does document how this default behaviour can be overridden.
You need this information when you are creating a table with, say, 125 partitions but have only 1 to a few thousand rows in each partition and have to explain why the table is taking up 1GB of space.
Also note that once a Partition has had an 8MB extent allocated to it, any new Partitions that you create out of splitting it will also hae 8MB extents allocated --even if they are empty !

.
.
.