Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Segment Creation on Demand (Deferred Segment Creation) in Oracle Database 11g Release 2

11.2.0.1

Basic Usage

Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving.

The functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default. It can be turned off using the following command.

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=[TRUE | FALSE];

The CREATE TABLE statement now supports the following segment creation clause.

SEGMENT CREATION { IMMEDIATE | DEFERRED }

The default action is DEFERRED, as shown below.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  clob_data CLOB,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data;

COLUMN segment_name FORMAT A30
SELECT segment_name,
       segment_type
FROM   user_segments;

no rows selected

SQL>

INSERT INTO tab1 VALUES (1, 'CLOB data for 1');

COLUMN segment_name FORMAT A30
SELECT segment_name,
       segment_type
FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB1                           TABLE
SYS_IL0000085548C00002$$       LOBINDEX
TAB1_PK                        INDEX
TAB1_CLOB_DATA                 LOBSEGMENT

4 rows selected.

SQL>

The DEFERRED clause can also be used explicitly.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  clob_data CLOB,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
SEGMENT CREATION DEFERRED
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data;

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

no rows selected

SQL>

The default behavior is altered by using the IMMEDIATE clause.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  clob_data CLOB,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
SEGMENT CREATION IMMEDIATE
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data;

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB1                           TABLE
SYS_IL0000085544C00002$$       LOBINDEX
TAB1_PK                        INDEX
TAB1_CLOB_DATA                 LOBSEGMENT

4 rows selected.

SQL>

Delayed Quota Errors

A rather annoying issue that results from deferred segment creation is that of delayed quota errors. Since the table creation doesn't result in segment creation, tables can be defined against any tablespace regardless of quotas. A quota error is only issued when an insert is attempted against the object.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER
)
TABLESPACE SYSTEM;

Table created.

SQL> INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'


SQL>

Export of Empty Objects Issues

Another annoying result of deferred segment creation is the un-patched "exp" utility doesn't support it properly. Tables with no segments don't get exported as expected. One solution is to turn off the functionality before creating any objects using the following command.

ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;

Alternatively, you can force the allocation of extents on any table with no rows using the following command.

ALTER TABLE tablename ALLOCATE EXTENT;

11.2.0.2

TRUNCATE TABLE .. DROP ALL STORAGE

The 11.2.0.2 patch introduces an addition to the TRUNCATE command. The default action is unchanged, but the DROP ALL STORAGE clause causes all segments associated with the table to be dropped.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  clob_data CLOB
)
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data;

INSERT INTO tab1 VALUES (1, 'Some CLOB data.');
COMMIT;

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB1                           TABLE
SYS_IL0000085289C00002$$       LOBINDEX
TAB1_CLOB_DATA                 LOBSEGMENT

3 rows selected.

SQL>

TRUNCATE TABLE tab1;

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB1                           TABLE
SYS_IL0000085289C00002$$       LOBINDEX
TAB1_CLOB_DATA                 LOBSEGMENT

3 rows selected.

SQL>

TRUNCATE TABLE tab1 DROP ALL STORAGE;

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

no rows selected

SQL>

DBMS_SPACE_ADMIN

The DBMS_SPACE_ADMIN package includes two new procedures to help manage segments associated with empty tables. The MATERIALIZE_DEFERRED_SEGMENTS procedure forces segment creation for objects whose segment creation has been deferred.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  clob_data CLOB
)
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data;

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

no rows selected

SQL>

CONN / AS SYSDBA

BEGIN
  DBMS_SPACE_ADMIN.materialize_deferred_segments (
    schema_name    => 'TEST',
    table_name     => 'TAB1',
    partition_name => NULL);
END;
/

CONN test/test

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
TAB1                           TABLE
SYS_IL0000085625C00002$$       LOBINDEX
TAB1_CLOB_DATA                 LOBSEGMENT

3 rows selected.

SQL>

The DROP_EMPTY_SEGMENTS procedure drops the segments for any tables with now no rows.

CONN / AS SYSDBA

BEGIN
  DBMS_SPACE_ADMIN.drop_empty_segments (
    schema_name    => 'TEST',
    table_name     => 'TAB1',
    partition_name => NULL);
END;
/

CONN test/test

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type
FROM   user_segments;

no rows selected

SQL>

The parameters are used as follows.

Partitioned Table Support

Deferred segment creation now supports partitioned tables, as shown by the following example.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  part_key  DATE,
  clob_data CLOB,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data
PARTITION BY RANGE (part_key) (
  PARTITION part_1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION part_2 VALUES LESS THAN (MAXVALUE) TABLESPACE users
);

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type,
       partition_name
FROM   user_segments;

no rows selected

SQL>

The default behavior can be overridden using the segment creation clause.

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id        NUMBER,
  part_key  DATE,
  clob_data CLOB,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
SEGMENT CREATION IMMEDIATE
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data
PARTITION BY RANGE (part_key) (
  PARTITION part_1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION part_2 VALUES LESS THAN (MAXVALUE) TABLESPACE users
);

COLUMN segment_name FORMAT A30

SELECT segment_name,
       segment_type,
       partition_name
FROM   user_segments;

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME
------------------------------ ------------------ ------------------------------
TAB1                           TABLE PARTITION    PART_1
TAB1                           TABLE PARTITION    PART_2
TAB1_PK                        INDEX
SYS_IL0000085525C00003$$       INDEX PARTITION    SYS_IL_P91
SYS_IL0000085525C00003$$       INDEX PARTITION    SYS_IL_P92
TAB1_CLOB_DATA                 LOB PARTITION      SYS_LOB_P89
TAB1_CLOB_DATA                 LOB PARTITION      SYS_LOB_P90

7 rows selected.

SQL>

Default Size of First Extent for Partitioned Tables

The default size of the first extent for partitioned tables has been increased from 64K to 8Mb. Although this isn't really a feature of segment creation on demand, it does mean the relative space saving it gives has increased in this version, because the default space wasted has increased.

DROP TABLE tab1;

CREATE TABLE tab1 (
  id        NUMBER,
  part_key  DATE,
  clob_data CLOB,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
SEGMENT CREATION IMMEDIATE
LOB(clob_data) STORE AS SECUREFILE tab1_clob_data
PARTITION BY RANGE (part_key) (
  PARTITION part_1 VALUES LESS THAN (TO_DATE('01/01/2011', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION part_2 VALUES LESS THAN (MAXVALUE) TABLESPACE users
);

COLUMN segment_name FORMAT A30

SELECT segment_name,
       partition_name,
       bytes/1024/1024 AS MB
FROM   user_segments;

SEGMENT_NAME                   PARTITION_NAME                         MB
------------------------------ ------------------------------ ----------
TAB1                           PART_1                                  8
TAB1                           PART_2                                  8
TAB1_PK                                                            .0625
SYS_IL0000085631C00003$$       SYS_IL_P111                         .0625
SYS_IL0000085631C00003$$       SYS_IL_P112                         .0625
TAB1_CLOB_DATA                 SYS_LOB_P109                            8
TAB1_CLOB_DATA                 SYS_LOB_P110                            8

7 rows selected.

SQL>

Export of Empty Objects Fixed

The issues related to export of empty objects mentioned earlier are fixed in 11.2.0.2.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.