Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







deferred segment creation tips

Oracle Database Tips by Donald BurlesonMarch 1,  2016

Question: What does deferred segment creation do?  Can you show an example of deferred segment creation?

Answer:  In Oracle 11g R2, one new feature was called segment creation on demand.  It quickly came to be known as deferred segment creation.

Touted as a space-saving feature, deferred segment creation was first made available in  The way deferred segment creation works, the space savings is particularly applicable to systems with a large number of empty tables.  In particular, it can save a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated. An added benefit of deferred segment creation is that it can also significantly reduce application installation time.

Essentially, deferred segment creation functioned initially such that for non-partitioned tables, none of the associated segments would be created until rows were inserted into the table.  The associated segments include the table itself as well as implicit index and LOB segments.

Deferred segment creation is controlled by the deferred_segment_creation initialization parameter.  The default setting for the deferred segment creation parameter is TRUE.  The setting can be toggled using the following syntax:

set deferred_segment_creation = [TRUE | FALSE]

Deferred segment creation is also supported by the CREATE TABLE command.  The syntax for this is:

segment creation { IMMEDIATE | DEFERRED }

DEFERRED is the default action for deferred segment creation, but it can be added explicitly as well.  The IMMEDIATE action must be added explicitly and effectively negates the space savings offered by deferred segment creation.

Most new features also come with new issues that have to be ironed out.  Deferred segment creation is no exception.  The following issues with deferred segment creation were noted by Oracle 11g R2 users:

  • Quota Errors:  Quota errors have resulted when an insert is issued against a table created against a tablespace with deferred segment creation enabled.  The table is reported as created, but with deferred segment creation, there are no resulting segment creations at the time of table creation.  This allows tables to be defined against any tablespace regardless of quotas. Therefore, the quota does not become an issue until the insert is attempted.
  • Export Issues: Using the pre-Oracle 10g upgraded export pump utility (exp) utility resulted in a failure to export tables with no segments properly.  One workaround is to use the following command to turn off deferred segment creation before creating any objects:

    alter system set deferred_segment_creation = FALSE

    For any table with no rows, an alternative would be to force extent allocation using the following command:

    alter table <tablename> allocate extent

Along comes

Fortunately, Oracle 11g R2 addressed some issues right out of the gate as well as introducing a couple of enhancements for deferred segment creation.

For example, the export issues inherent to were fixed in

The enhancements include:

  • Partitioned Tables:  Deferred segment creation is now supported for partitioned tables.  As with the non-partitioned tables, the default action for the CREATE TABLE command is DEFERRED; however, it can be overridden by setting it to IMMEDIATE as shown above.

  • TRUNCATE table:  The default action of the TRUNCATE command is unchanged; however, the new DROP ALL STORAGE clause can be used to drop all segments associated with the table.  The syntax for the command is:

    truncate table <tablename> drop all storage

  • dbms_space_admin:  In, dbms_space_admin has two additional procedures that can be used to help manage space issues associated with empty tables:
  1.  drop_empty_segments: This procedure drops the segments for tables with no rows.

      dbms_space_admin.drop_empty_segments (
        schema_name => '<schemaname>',
        table_name => '<tablename>',
        partition_name => NULL);

  2. materialize_deferred_segments:  For objects resulting from deferred segment creation, this procedure forces those objects to be created.

    .materialize_deferred_segments (
        schema_name => '<schemaname>',
        table_name => '<tablename>',
        partition_name => NULL);

The parameters are used as follows:

  • schema_name - This parameter tells Oracle to check all tables and their dependent objects in the specified schema. NULL is the default value of this parameter.  When using NULL, tables in all schemas will be checked. 
  • table_name - This paramter is used with schema_name to target a specific table and its dependents.
  • partition_name - This parameter is used with schema_name and table_name to target a specific partition and its dependents.

With powerful tools like deferred segment creation available, proper capacity planning is more critical than it has been before.  Just as the delayed quota errors are a function of mistakes in planning, other items such as disk space, I/O performance and even hardware acquisitions can be better managed with improved planning.

Deferred segment creation was introduced in and partly again in release, depending on the object type that is deferred creation.

The opposite of deferred segment creation is immediate segment creation with the default being deferred segment creation.

Please note the following on deferred segment creation:

  •  You can disable deferred segment creation by setting the initialization parameter deferred_segment_creation to false.
  • If you create a table/object with deferred segment creation you will not see the segment in dba_segments
  •  If you drop the tablespace the tablespace containing the object without the INCLUDING CONTENTS, the drop tablespace will succeed and the table will remain.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster