Segment creation on Demand

·         Benefits are 1) Save disk space 2) improve installation time of applications with large schema

·         Segment creation on demand is available for nonpartitioned tables

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE

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

deferred_segment_creation            boolean     TRUE

SQL> create table TRAX_OWNER.tab_seg(c1 number, c2 varchar2(200));

Table created.

SQL> select segment_name from dba_segments where segment_name = 'TAB_SEG';

no rows selected

SQL> insert into TRAX_OWNER.tab_seg values(1, 'aaaaaaaaaaaaa');

1 row created.

SQL> select segment_name from dba_segments where segment_name = 'TAB_SEG';

SEGMENT_NAME

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

TAB_SEG

Segment creation can be controlled in two ways: session or system level

SQL> alter session set deferred_segment_creation = true;

Session altered.

SQL> alter system set deferred_segment_creation = false;

System altered.