Sunday, February 13, 2011

11Gr2 new features :Deferred segment creation

Orignally appeared on my old blog Sep 2009
----
Looking at all the 11GR2 features. the Fact that Oracle decided to add deferred segment creation seems to be huge. For people that are space constained or cannot do manage capacity appropriately due to 100's of Tables (Siebel,Peoplesoft) .
According to Oracle Documentation There is a small penalty when the first row is created which migh be of concern to people who like to prevent  application impact by preallocating extents which can be achieved by  SEGMENT CREATION IMMEDIATE option of the CREATE TABLE. Capacity management will  also be key since Oracle Might have an unable to extend condition when the first row is inserted and there is no space for the extent to actually be created .
All* _SEGMENT VIEW will not have an entry till the first row is inserted  which help reports basedon those view stay the same. THE SEGMENT_CREATED COLUMN has been added to  _TABLES,INDEXES etc to show status  of DEFERRED .
There is an init parameter DEFERRED_SEGMENT_CREATION which can be set to FALSE is this behavior needs to be disabled. While i see utility in Apps style environment . I need to validate what the impact would be on ETL Loads for new tables.

Google Search