This page discusses deferred segment creation which was introduced in Oracle 11.2.
The page was developed in a Linux VM (OEL5U6) with single instance Oracle 11.2.0.3 database
In order to store data in a table or index, a segment must exist. Initially a segment will contain at least one extent. An extent is a contiguous set of blocks within a data file.
Prior to Oracle 11.2, when a table or index is created, the segment is also created. For most databases this is not an issue. However, it is common for packaged applications to install more tables and index than will ever be required. For example, some features of the application may not be licenced or configured. It is easier, however, to create all of the objects at install time to avoid an outage if additional functionality is subsequent required.
For some packages therefore, thousands of additional tables can be created, but not subsequently used. Although the object definitions do not occupy much space in the data dictionary, each table or index requires at least one extent. Each of these extents will need to be copied during a full backup. As they never change they will never be included in an incremental backup, though they may need to be read and skipped if block change tracking is not enabled.
Deferred segment creation allows table and index definitions to be added to the data dictionary for new objects, but the creation of segments to be delayed until the first row is added to the object.
Deferred segment creation is controlled by the DEFERRED_SEGMENT_CREATION parameter which defaults to TRUE.
Consider the following example (Oracle 11.2.0.3)
SQL> show parameter deferred_segment_creation NAME TYPE VALUE ------------------------------------ ----------- ------------------------- deferred_segment_creation boolean TRUE
SQL> CREATE TABLE t1 ( c1 NUMBER );
SQL> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);
Initially no segments will be created for either the table or the index:
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1'
AND segment_type = 'TABLE';
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1_PK'
AND segment_type = 'INDEX';
COUNT(*)
----------
0
If a row is added, for example:
SQL> INSERT INTO t1 VALUES (42); SQL> COMMIT;
When the first row is added, segments for both the table and index will be created:
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1'
AND segment_type = 'TABLE';
COUNT(*)
----------
1
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1_PK'
AND segment_type = 'INDEX';
COUNT(*)
----------
1
Now delete all rows from the table:
SQL> DELETE FROM t1; SQL> COMMIT;
The table and index segments are not deleted:
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1'
AND segment_type = 'TABLE';
COUNT(*)
----------
1
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1_PK'
AND segment_type = 'INDEX';
COUNT(*)
----------
1
Insert the row again and then truncate the table:
INSERT INTO t1 VALUES (42); COMMIT; TRUNCATE TABLE t1;
Again the segments for the table and index are not deleted:
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1'
AND segment_type = 'TABLE';
COUNT(*)
----------
1
SQL> SELECT COUNT(*) FROM dba_segments
WHERE owner = 'US01'
AND segment_name = 'T1_PK'
AND segment_type = 'INDEX';
COUNT(*)
----------
1
This feature is also useful for investigative purposes. For example, as part of my GoldenGate investigations, I am trying to establish when the GGS_MARKER table should be created. The GGS_MARKER table already exists in the schema of the GoldenGate user (GG01). The problem is that I have no idea why the table exists. It could be used for transient data which only exists for a short period. Therefore simply counting the number of rows is not definitive as the table may be empty.
In earlier releases, I could probably check if this table had ever been used by inspecting block dumps; Oracle rarely tidies up completely when rows are deleted from tables and indexes, so it is usually possible to spot evidence that data once existed. However, deferred segment creation provides a faster, more elegant way to determine the same information.
The above tests demonstrate that then as the DEFERRED_SEGMENT_CREATION parameter has the default value of TRUE, if the GGS_MARKER table has never been used, the table segment should not exist.
The following statement confirms this:
SQL> SELECT COUNT(*)
FROM dba_segments
WHERE owner = 'GG01'
AND segment_name = 'GGS_MARKER'
AND segment_type = 'TABLE';
COUNT(*)
----------
0
Note that deferred segment creation introduces some space management issues which might affect tools and scripts which use DBA_SEGMENTS to calculate available and used space in a database. Check that your tools support this feature when upgrading to Oracle 11.2.