deferred segment creation tips

Touted as a space-saving feature, deferred segment creation was first made available in 11.2.0.1.  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 11.2.0.1 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 11.2.0.2

Fortunately, Oracle 11g R2 11.2.0.2 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 11.2.0.1 were fixed in 11.2.0.2.

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 11.2.0.2, 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.
   
   begin
     dbms_space_admin.drop_empty_segments (
       schema_name => '<schemaname>',
       table_name => '<tablename>',
       partition_name => NULL);
   end;
   /

2. materialize_deferred_segments:  For objects resulting from deferred segment creation, this procedure forces those objects to be created.
   
   begin
     dbms_space_admin.materialize_deferred_segments (
       schema_name => '<schemaname>',
       table_name => '<tablename>',
       partition_name => NULL);
   end;
   /