Oracle11gR2 - Deferred Segment Creation / Segment Creation On Demand

DEFERRED_SEGMENT_CREATION is a new feature introduced in 11g R2. Where oracle will create tables, but will not allocate the initial storage immediately. Storage is allocated when first row is added in the table. In 11gR2 a new column segment_created added in dba_tables.

Example:-

Create table test(c1 char);

After creation of a table 1 entry will be added into dba_tables with segment_created column as ‘NO’.

– No entries in dba_segments

SQL> select segment_name, bytes from dba_segments where segment_name='TEST';

no rows selected

– Add a row in table

SQL> insert into test values ('X');

1 row created.

– Now we can see storage allocated for this table

SQL> select segment_name, bytes from dba_segments where segment_name='TEST';

SEGMENT_NAME BYTES

------------------------------ ----------

TEST 65536

– Check segment_created from dba_tables

SQL> select segment_created from dba_tables where table_name='TEST';

SEG

---

YES

This feature is driven by the parameter “deferred_segment_creation“, which is by default TRUE.

SQL> show parameter deferr

NAME TYPE VALUE

-------------------- ----------- --------

deferred_segment_creation boolean TRUE

We can override this seeting at session / system level

SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> show parameter deferr

NAME TYPE VALUE

---------------- ----------- ----------

deferred_segment_creation boolean FALSE

– Create another table for test

SQL> create table test (c1 number);

Table created.

– Storage is allocated without adding data (This was the default behaviour prior to 11gR2)

SQL> select segment_created from dba_tables

where table_name='TEST';

SEG

---

YES

We can also override this setting by specifying “segment creation” clause at table creation time.

– SEGMENT CREATION IMMEDIATE
– This will allocate storage irrespective of deferred_segment_creation settings

SQL> create table test (c1 number) segment creation immediate;

Table created.

– SEGMENT CREATION DEFERRED
– This will not create initial storage, if deferred_segment_creation is set to FALSE

SQL> create table test (c1 number) segment creation deferred;

Table created.