Oracle Deferred Segment Creation Tips

转自 http://www.dba-oracle.com/t_oracle_deferred_segment_creation.htm

As Oracle is known to do, features are added to new releases of the database.  In Oracle 11g R2, one such 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 11.2.0.1.  The way deferred segement 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:
 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;
/

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;
/

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 acqisitions can be better managed with improved planning.

Deferred segment creation was introduced in 11.2.0.1 and partly again in release 11.2.0.2, 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