Deffered Segment allocation in 11gR2

DEFERRED SEGMENT ALLOCATION IN ORACLE 11GR2

You’re installing an application that has thousands of tables and indexes. Each table and index are
configured to initially allocate an initial extent of 10 MB. When deploying the installation DDL to your
production environment, you want install the database objects as fast as possible. You realize it will take
some time to deploy the DDL if each object allocates 10 MB of disk space as it is created. You wonder if
you can somehow instruct Oracle to defer the initial extent allocation for each object until data is
actually inserted into a table.

SQL> sho parameter DEFERRED_SEGMENT_CREATION;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
deferred_segment_creation            boolean     TRUE

************************************************

create table f_regs(reg_id number, reg_name varchar2(200));

table created

************************************************

select count(*) from user_extents where segment_name='F_REGS';

COUNT(*)
0

***********************************************
Now insert a record


insert into f_regs values (1,'DUMMY');

COMMIT;


select count(*) from user_extents where segment_name='F_REGS';

COUNT(*)
1

************************************************
HOW IT WORKS
************************************************
Starting with Oracle Database 11g R2, with non-partitioned heap-organized tables created in locally
managed tablespaces, the initial segment creation is deferred until a record is inserted into the table.
You need to be aware of Oracle’s deferred segment creation feature for several reasons:

• Allows for a faster installation of applications that have a large number of tables
and indexes; this improves installation speed, especially when you have
thousands of objects.

• As a DBA, your space usage reports may initially confuse you when you notice that
there is no space allocated for objects.

• The creation of the first row will take a slightly longer time than in previous
versions (because now Oracle allocates the first extent based on the creation of the
first row). For most applications, this performance degradation is not noticeable.

You can disable the deferred segment creation feature by setting the database initialization
parameter DEFERRED_SEGMENT_CREATION to FALSE. The default for this parameter is TRUE.
You can also control the deferred segment creation behavior when you create the table. The CREATE
TABLE statement has two new clauses: SEGMENT CREATION IMMEDIATE and SEGMENT CREATION DEFERRED—for

example:

create table f_regs(
reg_id number
,reg_name varchar2(2000))
segment creation immediate;