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

advertisements

_____________________________________________________________________________________________________________________

• Deferred segment introduced in Oracle 11gR2 – 11.2.0.1

• 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 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: Using this procedure you can drop the segments for the tables with no rows.
begin
  dbms_space_admin.drop_empty_segments (
    schema_name => '<schema>',
    table_name => '<table>',
    partition_name => NULL);
end;
/

materialize_deferred_segments:  For objects whose segment creation has been deferred, this procedure forces those objects to be created.
begin
  dbms_space_admin.materialize_deferred_segments (
    schema_name => '<schem>',
    table_name => '<table>',
    partition_name => NULL);
end;
/

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