Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
So I decided to make a serie of posts with really quick notes about each one of them.
You can see all posts in this serie in my page of posts and some others more.
Ready? Here it goes:
Segment Creation on Demand or Deferred Segment Creation
I also talked about it in post EXP Missing Tables on 11.2.
Segment creation on demand, or deferred segment creation as it is also known, is a space saving feature of Oracle Database 11g Release 2. When non-partitioned tables are created, none of the associated segments (table, implicit index and LOB segments) are created until rows are inserted into the table. For systems with lots of empty tables, this can represent a large space saving. This functionality can be controlled by the DEFERRED_SEGMENT_CREATION initialization parameter, which is set to TRUE by default.
In this regard, an important aspect to be aware is that tool EXP will not export those objects unless it has the parameter VERSION=11.1 set. This happens because the deprecated tool EXP uses the view ALL_SEGMENTS to perform export and, once segments are not allocated, those objects are not listed and not exported. Besides setting parameter VERSION on export, this feature can be avoided by setting database parameter DEFERRED_SEGMENT_CREATION to FALSE. Objects already created with this feature enabled need to have segments allocated. This can be performed with command:
ALTER TABLE table_name ALLOCATE EXTENT
Also the procedure DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS can be used to force creating those segments.
Cheers!
