Features and Advantages of Oracle 11gR2 Deferred segments – Segment Creation on Demand



The Eucharistic Miracles of the World
  • Deferred segment introduced in Oracle 11gR2 –

  • It is known as segment creation on demand

  • It is a space saving feature for the database and disks

  • Deferred segments will not consume space for the table without any row. It is very useful for a database with large number of empty tables.

  • Deferred segment creation is controlled by deferred_segment_creation init parameter. By default is TRUE in 11gR2. Parameter usage is like this
Set deferred_segment_creation=[true/false]

  • Deferred segment can be mentioned with create table command as mentioned below.
Segment creation [IMMEDIATE/DEFERRED]

  • You can alter session or system level
Alter session set deferred_segment_creation=true
Alter system set deferred_segment_creation=true

  • If you query user_extents view before you insert any data, you will get a result as no rows which means no extents. There is no storage pre-allocation as in Oracle prior versions.

  • The column SEGMENT_CREATED in dba_tables view indicates that whether the initial segment was created or not. The value of the column would [yes/no]

  • This is very useful for the application tables which never get populated. There would not be any storage pre-allocation.

  • Deferred segment feature can be applied to almost all tables (all except partitioned tables, index-organized tables, clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables) , lobs and almost all indexes (all except partitioned indexes, bitmap join indexes, and domain indexes). The tables also cannot be created in SYS, SYSTEM, PUBLIC, OUTLN, or XDB schemas (this is probably not a big deal in your case).

  • You have to set the compatibility to ’11.2.0’

  • If you try to create tables in system schema or add partitions, you can be seeing that the initial segment was created. If you try to force on system table with DDL level option you will end up with error ORA-14223: Deferred segment creation is not supported for this table

·         dbms_space_admin:  In, dbms_space_admin has two additional procedures that can be used to help manage space issues associated with empty tables:
drop_empty_segments: Using this procedure you can drop the segments for the tables with no rows.
  dbms_space_admin.drop_empty_segments (
    schema_name => '<schema>',
    table_name => '<table>',
    partition_name => NULL);
materialize_deferred_segments:  For objects whose segment creation has been deferred, this procedure forces those objects to be created.
  dbms_space_admin.materialize_deferred_segments (
    schema_name => '<schem>',
    table_name => '<table>',
    partition_name => NULL);
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.


Website Stats


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)

acehints.com Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer