As you know Oracle Database 11g Release 2 was just released. As a result, it is time to start a series of post about some new features. I’ll start with one that is not really related to performance… In fact, this post is a kind of follow-up to a comment written by Bernd Eckenfels about one of my previous posts: System Managed Extent Size – 11g Improvements. The following is an excerpt of Bernd’s comment:
One thing about uniform size and initial segments I hate is, that an empty segment (lob column) always uses up one extend. This wastes a lot of storage if the col is unused. And as a software vendor you never know when a customer will use the segment.
I agree with Bernd, this might be a real problem when you have a lot of unused segments. Oracle also recognized this issue and, in Oracle Database 11g Release 2, provides a partial (yes, only partial…) solution: deferred segment creation. The idea of deferred segment creation is very simple. The segments related to a table or an index are not immediately created when the CREATE TABLE or CREATE INDEX statement is executed, but only when the first row is inserted into the table. Let’s have a look to an example:
SQL> CREATE TABLE t (n number); Table created. SQL> SELECT segment_created 2 FROM user_tables 3 WHERE table_name = 'T'; SEGMENT_CREATED --------------- NO SQL> SELECT segment_name 2 FROM user_segments 3 WHERE segment_name = 'T'; no rows selected SQL> INSERT INTO t VALUES (1); 1 row created. SQL> SELECT segment_created 2 FROM user_tables 3 WHERE table_name = 'T'; SEGMENT_CREATED --------------- YES SQL> SELECT segment_name 2 FROM user_segments 3 WHERE segment_name = 'T'; SEGMENT_NAME ------------------------------ T
It is important to point out that all segments related to a table are created when the first row is inserted into it. And that, even if they are not used to store data. For example, the segments for an index or a lob are created even if they are not used. This is the reason why I wrote that this is only a partial solution to the problem mentioned by Bernd. The following example illustrates this for a lob:
SQL> CREATE TABLE t (n number, c clob, b blob); Table created. SQL> SELECT column_name, segment_name 2 FROM user_lobs JOIN user_segments USING (segment_name) 3 WHERE table_name = 'T'; no rows selected SQL> INSERT INTO t (n) VALUES (1); 1 row created. SQL> SELECT column_name, segment_name 2 FROM user_lobs JOIN user_segments USING (segment_name) 3 WHERE table_name = 'T'; COLUMN_NAME SEGMENT_NAME ------------ ------------------------------ C SYS_LOB0000073904C00002$$ B SYS_LOB0000073904C00003$$
In addition, the feature doesn’t work in all situations (yet). In other words, there are some restrictions. The most important, in my opinion, is that only non-partitioned table can take advantage of it. A full list of restrictions is obviously available in the documentation.
Note that deferred segment creation is enabled by default. To enable/disable it at the session or system level, you can change the initialization parameter DEFERRED_SEGMENT_CREATION. In addition, it’s also possible to enable/disable it for a single table by specifying the deferred segment creation clause.
Cool
Good to know. No more emptylob tablespaces and manual keepin track of them 
Cool stuff.
But it seems weird that Oracle has determined to have this feature enabled by default, which would make many people embarassed without knowing this new feature.
Just FYI…though the documentation illustrates the use of ALTER TABLE statement for modifying the SEGMENT CREATION clause, but apparently, it doesn’t work. It errors out stating that it’s an INVALID alter table command.
I would join Chandra, I was trying to ALTER TABLE, but so far no luck.
I opened a thread in OTN Forum to see if someone alread got it work
http://forums.oracle.com/forums/thread.jspa?threadID=955170&tstart=0
Let see.
An other note about that feature, once you got a segment for a table, it does not look so easy to get
rid off, I tried many different things by expdp/impdp but everytime the segment is came back, see my test
http://gasparotto.blogspot.com/2009/09/get-rid-off-segment-of-empty-table.html
Nicolas.
I’ve asked Nicolas Gasparotto to test the actual use of the deferred segment allocation and its practical application with an ALTER TABLE MOVE, which is specifically documented as valid in the manual.
Nicholas was most kind in providing the test bed. This was a follow-up from the discussion we had about it in my blog.
Unfortunately, it doesn’t work. The software refuses the ALTER TABLE MOVE with a syntax error.
Ah well: it was a good idea. As usual, the implementation by Oracle let it down…
Maybe this will work properly in 11gr3?
Thank you all for providing interesting information about this topic. Honestly, I didn’t thought to test the ALTER TABLE. What I did test however, is the TRUNCATE TABLE. In other words, I wanted to know whether a TRUNCATE removes the segments… It is not the case. What a pity!
Cheers,
Chris
Well, I raise a SR to help on the doc reading about that topic.
ALTER TABLE MOVE path does not provide SEGMENT CREATION clause :
move_table_clause -> segment_attributes_clause -> physical_attributes_clause -> storage_clause
The right path to reach this clause is the following :
ALTER TABLE > column_clause > add_column_clause > column_properties > nested_table_col_properties > physical_properties > deferred_segment_creation > segment_attributes_clause
If you read it correctly, that won’t work for standard table and “common”
ALTER TABLE statement.
So, no luck on the way we would expected.
Nicolas.
Also note that tables created in sys or system schema will have a segment associated to it.
So once again: SYS and SYSTEM users are special!
Nicolas/Christian,
Looks like the ALTER TABLE command is not expected (or supposed) to change the SEGEMENT CREATION attribute. It might have just found a place in the documentation in error (again, this is just my guess based on the following finding!). Here is what I did:
1) Created a table with deferred segment creation
2) Another table without deferring segment creation
3) Used DBMS_METADATA_DIFF to see the difference between the structures, here is what it says:
SQL> Select dbms_metadata_diff.compare_alter(‘TABLE’,'APP_DATA’,'APP_DATA_TEMP’, ‘CHANDRA’,'CHANDRA’,null,’BDBE01′) DIFF from dual;
DIFF
——————————————————————————–
– Cannot ALTER to make segment creation deferred TABLE “CHANDRA”.”APP_DATA”
ALTER TABLE “CHANDRA”.”APP_DATA” RENAME TO “APP_DATA_TEMP”
Hope it makes sense.
Thanks
Chandra
How useless … Major points:
- doesn’t work on partitioned tables (although interval partitioning somewhat offsets this but still)
- truncate doest not remove
Why bother implementing it properly … I am sure it’s complicated, but still …
Just use:
alter table allocate extent;
to allocate segment extents.
Hi Anthony
What is the purpose of doing so? Sorry, but I don’t understand… I mean, here the point is to *not* allocate extents…
Cheers,
Chris
Users of deprecated tools beware! exp (the conventional data export tool) does not export tables that have not yet had their segment allocated.
I had to downgrade from EE 11.2 to SE 11.2, I was unable to impdp the schemas from EE because of “feature not available” regarding the “deferred segment creation” which is default. Now I deleted EE Database and I can’t import schemas on the new (SE) one. I can’t figure how to complete my porting. Any suggestion??
Ciao Giuseppe
Ufff… really weird situation… I didn’t know that it was an EE edition feature.
To solve the problem I would try to create the tables before importing the data.
If you do not have the scripts to create the tables, you should be able to extract the DDL from the DP file with the SQLFILE option.
HTH
Chris
I just wrote a new post that shows how to get rid of empty segments. Refer to Deferred Segment Creation as of 11.2.0.2.
in case getting error,
ORA-00439: feature not enabled: Deferred Segment Creation
what can you do?
Hi Avi
You are getting this error because deferred segment creation is only available in Enterprise Edition (see http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB).
But, to answer your question, it would be necessary to know how you get it. In fact, if you ask the question, I guess that you know this restriction and, therefore, it’s not because you specified the “segment creation deferred” option in a CREATE TABLE statement… So, let me know what you are doing.
Cheers,
Chris