DEFERRED_SEGMENT_CREATION parameter  specifies how segments will be created for the table and its objects like index.

Setting this parameter to true will save disk space, if we expect lot of empty tables in the database.

Lets see the below example:->

When DEFERRED_SEGMENT_CREATION=TRUE( THIS IS THE DEFAULT ONE)

QL> show parameter DEFERRED_SEGMENT_CREATION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
deferred_segment_creation            boolean     TRUE
SQL>
SQL>

SQL> create table DEF_TABLE ( class_name VARCHAR2(100), SECTION NUMBER);

Table created.



SQL>  create index DEF_IDX on DEF_TABLE (SECTION);

Index created.


SQL>  select segment_name,segment_type from dba_segments where OWNER='DBACLASS';

no rows selected.

We can see, it haven’t created the segments. So if you expect lot of empty tables in your database, setting this parameter to TRUE, will save lot of disk space.

Now set to FALSE, do the same test:

SQL> alter system set deferred_segment_creation=FALSE scope=both;

System altered.


SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
deferred_segment_creation            boolean     FALSE

SQL>  create table DEF_TABLE_2 ( class_name VARCHAR2(100), SECTION NUMBER);

Table created.



SQL>  create index DEF_IDX2 on DEF_TABLE_2(SECTION);

Index created.





SQL>  select segment_name,segment_type from dba_segments where OWNER='DBACLASS';

SEGMENT_NAME                                                            SEGMENT_TYPE
---------------------------------------------------------------------- ------------------
DEF_IDX2                                                               INDEX
DEF_TABLE_2                                                            TABLE

We can see, as soon as we create the table and and index, the segment got created ,despite it does not   contain any data.