Deferred_segment_creation of the 11g Parameter

Deferred_segment_creation of the 11g Parameter

Deferred_segment_creation

Boolean

The default value is true.

This parameter is not found in 10gR2, and is available in 11g.

It can also be seen from the literal meaning that when this parameter is set to true, when a table is created, no segment is assigned to it, that is, no space is allocated, segment is allocated only when the first data entry is inserted into this table.

The advantage of setting to true: when creating a series of tables, if you know that many tables do not have data, you can specify this parameter to true. This saves space and increases the table creation speed.

Disadvantage: When exp is used for export, tables without segment allocation are not exported. You can use expdp to export empty tables.

You can view the current settings of this parameter.

show parameter deferred_segment_creation

This parameter can be dynamically modified.

alter system/session set deferred_segment_creation=true/false;

After the change, the tables that delayed segment allocation are not affected and are still empty.

You can use the following statement to query all tables in a schema with no segment allocated:

select *  from user_tables  where segment_created = 'NO'

To allocate space for tables with no segment allocated, use the statement

select 'alter table ' || table_name || ' allocate extent;'  from user_tables where segment_created = 'NO'

Assign segment to all empty tables, and then export exp.

If the table is truncate after data is inserted, the segment is retained.