Creating Tables Without Segments - Oracle 11g Release 2 New Feature

Understand Deferred Segment Creation

Beginning with Oracle Database 11g Release 2, when you create heap-organized tables in a locally managed tablespace, the database defers table segment creation until the first row is inserted.
In addition, segment creation is deferred for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table.
In Release, deferred segment creation is not supported for partitioned tables. This restriction is removed in release and later.
The advantages of this space allocation method are the following:
  • It saves a significant amount of disk space in applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
  • It reduces application installation time.
There is a small performance penalty when the first row is inserted, because the new segment must be created at that time.
To enable deferred segment creation, compatibility must be set to '11.2.0' or higher.
The new clauses for the CREATE TABLE statement are:
These clauses override the default setting of the DEFERRED_SEGMENT_CREATION initialization parameter, TRUE, which defers segment creation. To disable deferred segment creation, set this parameter to FALSE.
Note that when you create a table with deferred segment creation, the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
You can verify deferred segment creation by viewing the SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS views for non-partitioned tables, and in *_TAB_PARTITIONS, *_IND_PARTITIONS, and *_LOB_PARTITIONS views for partitioned tables.

Reference Source - 


  1. I appreciate the time and effort you put into this text. This information is both useful and lavish. I'd like to express my gratitude to everyone who contributed to this text. Vietnam Export Data

  2. สมัครสมาชิก pxj เข้าสู่ระบบ pg เปิดเว็บบราวเซอร์ และไปที่เว็บไซต์ pg หรือเว็บไซต์ที่ต้องการเข้าสู่ระบบการเข้าสู่เว็บไซต์เกมหรือบริการออนไลน์มักเป็นกระบวนการที่ง่ายและสะดวก


Post a Comment

Popular posts from this blog

Oracle Architecture - Explained In Detailed - Administration I

Oracle Database SQL - Practise - Question - Scott Schema Examples

Step by Step - How to resolve redo log file corruption using ALTER DATABASE CLEAR UNARCHIVED LOGFILE command