In Oracle 11G Release 2 segment allocation has been changed. When new table is created, the table segment creation is postponed till first row insert.

This functionality is enabled by default with new parameter DEFERRED_SEGMENT_CREATION set as default to TRUE

DEFERRED_SEGMENT_CREATION = TRUE | FALSE

This new feature allows to save initially a lot of space for newly created objects especially when you have thousands of partitions. However you need to remeber about careful space planing.

You can control deferred segment creation by using following methods

• Parameter DEFERRED_SEGMENT_CREATION
  • specified as initialization parameter file
  • ALTER SESSION command

    alter session set DEFERRED_SEGMENT_CREATION=TRUE;

  • ALTER SYSTEM command

    alter system set DEFERRED_SEGMENT_CREATION=FALSE;

• With SEGMENT CREATION clause:
  • IMMEDIATE

    create table ...
    segment creation immediate

  • DEFERRED (default in Oracle Database 11g Release 2)

    create table ...
    segment creation deferred

Example

show parameter deferred_segment_creation

NAME                        TYPE        VALUE  
--------------------------- ----------- -------
deferred_segment_creation   boolean     TRUE   

create table test_tbl1
(
  id number
);

table TEST_TBL1 created.

create table test_tbl2
(
  id number
)
partition by range(id)
(
  partition p1 values less than(10),
  partition p2 values less than(20)
);

table TEST_TBL2 created.

select segment_name, partition_name
from user_segments
where segment_name in ('TEST_TBL1', 'TEST_TBL2');

no rows selected

once data are inserted into the tables segments are allocated

insert into test_tbl1 values(5);
insert into test_tbl2 values(5);
insert into test_tbl2 values(15);

select segment_name, partition_name
from user_segments
where segment_name in ('TEST_TBL1', 'TEST_TBL2')
order by 1 ,2;

SEGMENT_NAME    PARTITION_NAME   
--------------- -----------------
TEST_TBL1                         
TEST_TBL2       P1                
TEST_TBL2       P2

change parameter to disable deferred creation of segments for tables “deferred_segment_creation=FALSE” but still you can control it by using clause “segment creation deferred”.

alter session set deferred_segment_creation=FALSE;

create table test_tbl3
(
  id number
);

create table test_tbl4
(
  id number
) segment creation deferred;

select segment_name, partition_name
from user_segments
where segment_name in ('TEST_TBL3', 'TEST_TBL4')
order by 1 ,2;

SEGMENT_NAME    PARTITION_NAME   
--------------- -----------------
TEST_TBL3                         

insert into test_tbl4 values(15);

select segment_name, partition_name
from user_segments
where segment_name in ('TEST_TBL3', 'TEST_TBL3')
order by 1 ,2;

SEGMENT_NAME    PARTITION_NAME   
--------------- -----------------
TEST_TBL3                         
TEST_TBL4

New column SEGMENT_CREATED can be found in views USER_TABLES, USER_INDEXES, USER_LOBS that informs if segment is created.

Have a fun 🙂

Tomasz