Deferred Segment Creation

Property Description
Parameter type Boolean
Default value true
Range of values true | false
Basic No

DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. The design goal here is to prevent hundreds or thousands of segments being created by a 3rd party application that only uses a few of the tables it creates. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table. Before creating a set of tables (or installing an application), if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimises install time. This feature was first introduced in 11g.

Deferred segment creation allows tables and indexes to be created without physical segments which can be a double-edged sword as it allows objects to be created even if there is no space available for those objects in the specified tablespace. The data dictionary contains the object information so a query of DBA_TABLES, DBA_INDEXES or any of the %TAB% and %IND% tables and views will return the expected information. Querying DBA_SEGMENTS, however, may fail to return data since an object may not have any segments associated with it.

You should note that you can change this default behaviour (i.e. turn off deferred segment creation) by either:
  • at the database level via your init/spfile
  • at the session level via “alter session set deferred_segment_creation = false;”
  • statement by statement via “create table t ( x int ) segment creation immediate;”

Should you use deferred segment creation? That choice is one both the DBA team and development team need to make. Look at all aspects of the argument then decide accordingly. Remember that the ‘right’ answer is the one that’s suited to your environment.