Deferred_segment_creation in 11gR2

Use Deferred_segment_creation parameter to determine when the database should create the segment(s) for tables

rajesh@ORCL> select * from v$version;

BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.40

rajesh@ORCL> show parameter deferred_segment;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
deferred_segment_creation            boolean     TRUE
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> create table t(
  2     x number constraint t_pk primary key,
  3     y number constraint t_uk unique,
  4     z clob )
  5     lob (z)
  6     store as t_z_lob;

Table created.

Elapsed: 00:00:02.46
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> select segment_name, segment_type
  2  from user_segments
  3  /

no rows selected

Elapsed: 00:00:00.03
rajesh@ORCL>
rajesh@ORCL>
rajesh@ORCL> drop table t purge;

Table dropped.

Elapsed: 00:00:00.32



Setting the parameter deferred_segment_creation to TRUE will prevent underlying segments to be created, unless data gets actually inserted.

rajesh@ORCL>
rajesh@ORCL> alter session set deferred_segment_creation = false;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORCL>
rajesh@ORCL> create table t(
  2     x number constraint t_pk primary key,
  3     y number constraint t_uk unique,
  4     z clob )
  5     lob (z)
  6     store as t_z_lob
  7  /

Table created.

Elapsed: 00:00:01.07
rajesh@ORCL>
rajesh@ORCL> select segment_name, segment_type
  2  from user_segments
  3  /

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------
T                                                      TABLE
SYS_IL0000074718C00003$$       LOBINDEX
T_PK                                               INDEX
T_UK                                               INDEX
T_Z_LOB                                        LOBSEGMENT

Elapsed: 00:00:00.03
rajesh@ORCL>
rajesh@ORCL>