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

CREATE TABLE .... SEGMENT CREATION IMMEDIATE

or

CREATE TABLE ... SEGMENT CREATION DEFERRED

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

 

alter system set deferred_segment_creation=true

SQL>   select parameter, value from v$option;
PARAMETER                                VALUE
—————————————- ———–
Block Media Recovery                     TRUE
Fine-grained Auditing                    TRUE
Application Role                         TRUE
Enterprise User Security                 TRUE
Deffered Segment Creation          TRUE
37 rows selected.
SQL>