what’s new in Oracle 11g R2 – DEFERRED SEGMENT CREATION

This is really small but very useful feature. It was published in ASK TOM (please read it, it’s very interesting as most of Tom’s posts), Tom did not mention the exclusions, so if you follow his samples and create tables in SYSTEM schema or add partitions, you can be surprised either with the fact that the initial segment was created or if you try to force in on table DDL level with error ORA-14223: Deferred segment creation is not supported for this table

If you create a table (until oracle 11.2 R2), Oracle database allocates space for initial segment. This might be very small segment (if you want), it can be large segment if you expect a lot of data, this new feature allows you to defer creation of this initial segment until you load some data to the table and thus you can save some space in case you have a lot of tables (and maybe never plan to populate them – in case of large SAP installation for example). There is very small timing penalty if you create the initial segment when you load the data vs create the segment in advance (this is usually very tiny and I personally don’t see any big risk here).

There is of course “small” but … This feature can be applied to almost all tables (all except partitioned tables, index-organized tables, clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables) , lobs and almost all indexes (all except partitioned indexes, bitmap join indexes, and domain indexes). The tables also cannot be created in SYS, SYSTEM, PUBLIC, OUTLN, or XDB schemas (this is probably not a big deal in your case). Also please note you have to have compatibility set to ‘11.2.0’ .

Small exclusions huh?

One interesting by-product is this is that since you don’t pre-allocate any extents, you do not need quota on the tablespace you create the table in.

in Oracle 11.2 R2 it can be a bit space-saving exercise and is actually very easy. You can alter session or system or set it on table DDL level, the default is TRUE.

alter session set deferred_segment_creation=true;

or you can set it on system wide level

alter system set deferred_segment_creation=true;

and then create table as usual or set this on table level only using




1. If you query user_extents view before you insert any data, you can see that there are no rows = no extents (nothing is pre-allocated).

2. if you query dba_tables view, you can see that there is new column called SEGMENT_CREATED which indicates whether the initial segment was created or not YES/NO


2 thoughts on “what’s new in Oracle 11g R2 – DEFERRED SEGMENT CREATION

  1. Hello to all, it’s truly a good for me to visit this site, it includes priceless Information.

  2. What’s Happening i am new to this, I stumbled upon this I’ve found
    It absolutely useful and it has helped me out loads.
    I am hoping to give a contribution & aid other customers like its helped
    me. Good job.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: