Oracle Deferred Segment Creation

Starting 11g Release 2, we can create a table/index/LOB without space allocated to it (no segment creation) until first row is inserted into the table. Init parameter DEFERRED_SEGMENT_CREATION is used to setup the default behavior. By default value of this parameter is set to TRUE, which means that whenever a table will be created,
there will be no initial space allocation (segment creation) to the table, it’s indexes and LOB, until first row is inserted in this table. Value of this parameter can also be set at the session by using ALTER SESSION command.

If value of DEFERRED_SEGMENT_CREATION is set to FALSE (at system level or session level), a segment would immediately be created with the creation of a table. A table/index/LOB can still be created without a segment by using clause SEGMENT CREATION DEFERRED clause with the CREATE TABLE command as it will override the value of parameter DEFERRED_SEGMENT_CREATION. Likewise if value of this parameter is TRUE, still segment creation can be done immediately with the creation of table by creation clause SEGMENT CREATION IMMEDIATE with the CREATE TABLE command.

People who are not familiar with this feature can find themselves perplexed if they see their tables in USER_TABLES view, but no entry for these tables under DBA_SEGMENTS or USER_SEGMENTS views. The reason is same as explained above that if there is no data insertion in these tables, there will not entry in *_SEGMENTS data dictionary views.

Following is an example

SQL> show parameter deferred_segment_creation NAME                                 TYPE        VALUE ------------------------------------ ----------- ---------------- deferred_segment_creation            boolean     TRUE SQL> create user salman identified by salman default tablespace users quota unlimited on users; User created. SQL> grant connect, create table to salman; Grant succeeded. SQL> conn salman/salman Connected. SQL> create table test (id number); Table created. SQL> select segment_type from user_segments where segment_name='TEST'; no rows selected SQL> create index test_idx on test(id); Index created. SQL> select segment_type from user_segments where segment_name='TEST_IDX'; no rows selected

Segments get created after inserting one row

SQL> insert into test values(1); 1 row created. SQL> select segment_type from user_segments where segment_name='TEST'; SEGMENT_TYPE ------------------ TABLE SQL> select segment_type from user_segments where segment_name='TEST_IDX'; SEGMENT_TYPE ------------------ INDEX

Overriding default behavior if DEFERRED_SEGMENT_CREATION is set to TRUE

SQL> create table test (id number) segment creation immediate; Table created. SQL> select segment_type from user_segments where segment_name='TEST'; SEGMENT_TYPE ------------------ TABLE SQL> create index test_idx on test(id); Index created. SQL> select segment_type from user_segments where segment_name='TEST_IDX'; SEGMENT_TYPE ------------------ INDEX