Deferred Segment Creation—-Oracle 11gR2 new feature.

Hi…

Today i tested the new feature of Oracle 11gR2 that is Deferred Segment Creation….

In the previous versions, whenever any segment is created, it consist of atleast one Extent…But starting from Oracle 11gR2, it has changed…or we can say we have the option to create an empty segment or we should say object with no extents initially..Extents get allocated once object has some data..and we gets an entry for segments then…

What it does is, it will create the segment only when data is inserted..before that object has entry to the data dictionary but no corresponding segment is there..

Here is a small demo for this..

Now create an empty table. I created it in SCOTT schema as a copy of EMP table

So, table with name EMP_TEST is created as we can see the result of “Select * from tab”.

Now see how many extents have been allocated for this. We will query USER_SEGMENTS for this.

So here are the result from USER_SEGMENTS…and surprise..there is no entry for new table “EMP_TEST”..

I tried to export the table using EXP utility…see what happened…

Wait….whats this…we created the table..but exp says…SCOTT,EMP_TEST doesnot exist..

Whats this????

This is because no extent is allocated for the table yet…

Now insert some rows into the table…

So we have inserted some rows in the table..Now check if segment is created or not?

So here it comes as expected…Segment is now created..

Now if we try to export the table..it will also get exported..

so its done…..

Regards,

Navneet….