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