October 9, 2009

Deferred Segment Creation: segmentless tables (11gR2 new feature) and dumb questions

Much have been said about segmentless tables but most people have been asking me how does that work, as if that would be a complicated feature.

From 11g Release 2 all tables created without rows, do not create any segments (by default). If you want to change this behaviour, tweak the spfile parameter "deferred_segment_creation" to FALSE. Anyway the people installing Peoplesoft, SAP, Siebel and other thousand-table data models, really do thank for this new feature.

How does it work?

If the INITIAL extent of a table is 15 MegaBytes in size, but the table is empty, the table won't ask for that 15 MB of space. As soon as the first row of data is inserted (even a few bytes), the Oracle space engine will run the storage space settings of the table, before inserting that line, making the table actually owner of that space. You can create a 100GB table onto a 100MB tablespace as long as the table is empty, because the space will only be reclaimed by the table when it receives the first row.

Take a look at an example I've prepared to show the basic behaviour:
Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hr.xpto (n number, n2 varchar2(90))
2 storage (initial 15m) tablespace example;

Table created.

SQL> r
1* select blocks, bytes/1024/1024, segment_name, segment_type from dba_segments where segment_name = 'XPTO'

no rows selected

SQL> save expto

Created file expto.sql

Why aren't there any blocks for the table? Watch this parameter:
SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE

If I don't have 15BM in the tablespace EXAMPLE the INSERT will fail since that assessment would only be made now:
SQL> insert into hr.xpto values (1234,'Some String');
insert into hr.xpto values (1234,'Some String')
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 3 in tablespace EXAMPLE

As soon as I give more space to the tablespace, the insertion goes smoothly and the space is reclaimed:
SQL> insert into hr.xpto values (1234,'Some String');

1 row created.

SQL> @expto

---------- --------------- -------------------- ------------------
1920 15 XPTO TABLE

I love dumb questions!

Here's the 1.000.000 Euro question, which is simultaniously the dumbest:

"Will Oracle turn the table into segmentless again when I delete all rows?"

I love this kind of line of thought that you can only find in very honest and dumbest people! But they're people, and they have sentiments,

so you should respect their lines of thought and don't reject them at start.

I learn a lot from the dumbest questions people make, and so I stimulate people to ALWAYS ASK.

So let me prepare an answer to that question in 2 sentences:

"No. Oracle won't remove all segments if you clear the table."
"Yes, if you want to downsize the table segment-wise, you can do it. Yes you can!"

Want to see it?
SQL> truncate table hr.xpto;

Table truncated.

SQL> @expto

---------- --------------- -------------------- ------------------
1920 15 XPTO TABLE

Truncating the table didn't shronk the table. It still has the 15MB.

Now let's try to shrink it in two methods: 1) Explicitly deallocating all the extents 2) Shrink clause

1) Deallocate:
SQL> alter table hr.xpto deallocate unused keep 0;

Table altered.

SQL> @expto

---------- --------------- -------------------- ------------------
24 .1875 XPTO TABLE

When were left with 24 blocks, which is one segment of 192KB.

2) Shrink clause:

We have to enable row movement first:
SQL> alter table hr.xpto enable row movement;

Table altered.

SQL> alter table hr.xpto shrink space;

Table altered.

SQL> @expto

---------- --------------- -------------------- ------------------
24 .1875 XPTO TABLE

Exactly the same.

But still, it ain't possible to transform a table that once had a segment into a segmentless object.



  1. This new feature is only nice for new database. I also tried many thing to "remove" a created segment from a table becoming empty, even expdp/impdp does not help and so far, that's not possible.
    However, from the documentation point of view, that would be possible to do an ALTER TABLE xxx MOVE to get rid of the empty segment. It looks there is some bugs (TBC). Find out more here (see the comments) :


  2. Thanks for the comment Nicolas. Leker!
    I totally agree with Nuno Souto when he says these new implementations are "half-brained".


  3. what I need to do with this kind of error:
    ORA-00439: feature not enabled: Deferred Segment Creation

  4. Probably you have Standard Edition. Also in there are a lot of new functionalities regarding this feature.

  5. just for the records, in oracle provided this feature - even with a new package and not built into existing statements.

  6. Thanks Martin.
    For those out there wanting to get your hands dirty with these new procedures, please check out the docs here: