Monday, February 14, 2011

Deferred Segment Creation...

Something I learned new this morning...

Oracle 11g introduced the new feature "deferred segment creation". In a nutshell - it is a new (default!) feature whereby when you create a new table - no segment is created, no initial extent is allocated, no storage is reserved.

The design goal was to prevent hundreds or thousands of segments being created by a 3rd party application that only uses 100 of the tables it creates. Many 3rd party applications create every possible table they might use - only to use 100 of them given the feature set you use.

So, the upside of this feature is that you save space, you don't clutter up your data dictionary. Sounds all good - but could there be downsides? Anytime the default way the database operates changes - there could be downsides.

One 'obvious' side effect of deferred segment creation could be seen in an installation script that installs all of its tables, indexes, etc and then counts USER_SEGMENTS rows to ensure everything was installed. The count would not be ZERO instead of however many segments were created.

Another one, one that I just became aware of this morning, is that you can now create a table in a tablespace which you have NO QUOTA on. In the past - you would expect this as the logical outcome of not having any quota on a tablespace:

ops$tkyte%ORA10GR2> create user a identified by a default tablespace users;
User created.

ops$tkyte%ORA10GR2> grant create session, create table to a;
Grant succeeded.

ops$tkyte%ORA10GR2> connect a/a

a%ORA10GR2> create table t ( x int primary key );
create table t ( x int primary key )
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

However in 11g - you'll find this behavior instead:

ops$tkyte%ORA11GR2> create user a identified by a default tablespace users;
User created.

ops$tkyte%ORA11GR2> grant create session, create table to a;
Grant succeeded.

ops$tkyte%ORA11GR2> connect a/a

a%ORA11GR2> create table t ( x int primary key );
Table created.

I just created a table in a tablespace I have no quota on - successfully. Or did I?

a%ORA11GR2> insert into t values ( 1 );
insert into t values ( 1 )
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Apparently - not really. The table created - but the segments cannot be created. The ORA-1950 is not an error you would be expecting on an INSERT - you might expect "unable to extend", but not an ORA-1950 in general.

You should note that you can change this default behavior - that is turn off deferred segment creation either

  • at the database level via your init/spfile
  • at the session level via "alter session set deferred_segment_creation = false;"
  • statement by statement via "create table t ( x int ) segment creation immediate;"
if you like. Things change over time...


Anonymous Anonymous said....

sometimes export tool (exp) also exports smaller number of tables than you would expect :)


Mon Feb 14, 09:41:00 AM EST  

Blogger Marcus said....

Further downsides: You can not ("ORA-00439: feature not enabled: Deferred Segment Creation") import tables with the "segment creation deferred" option with Datapump into a Standard Edition, if the dump comes from an Enterprise Edition.
If you now think conventional export - no, exp won't even export empty tables that have no segments.

Mon Feb 14, 09:42:00 AM EST  

Blogger Bradd Piontek said....

anonymous: Here's the rub. the 'exp' utility is no longer supported in Oracle 11g and greater. It is there, but no changes are being made to it. Only the 'imp' utility is supported. Time to start using Data Pump.

Mon Feb 14, 11:29:00 AM EST  

Blogger Laurent Schneider said....

another side effect : if no table segment, then no index segment !

but it is still a great feature that does save a lot of space and time when installing new releases with plenty of empty tables.

Mon Feb 14, 11:30:00 AM EST  

Blogger Vojislav Depalov said....

I've have a problem with that. We delivers database in form of empty (no data) database backup. And when this database backup is imported, tables without data in it do not create, and all other code(views, triggers, procedures) fail, and can not be compiled. But, fortunately we made to find that parameter which returns thing in "normal" = Oracle 10g behavior.

Mon Feb 14, 03:08:00 PM EST  

Blogger Vladimir Begun said....


Table created.


Table created.


1 row created.


1 row created.

SQL> SELECT initial_extent FROM user_tables WHERE table_name = 'T1';


SQL> SELECT initial_extent FROM user_tab_partitions WHERE table_name = 'T2';



SQL> SELECT 8388608 / 65536 FROM dual;


:-) Yet another 'small' change.

Mon Feb 14, 07:02:00 PM EST  

Blogger Noons said....

I'd love to see what happens to the CBO when it joins one of these empty tables to one with data!

Mon Feb 14, 11:22:00 PM EST  

Anonymous Anonymous said....

I don't like this at all....

Oracle changed their software to do something unintuitive and confusing because of bad vendor products?


Tue Feb 15, 12:39:00 PM EST  

Blogger Cujo said....

DB2 introduced this "feature" a little while ago. I suppose Oracle is trying to keep up. It's called a virtual table in DB2 and is stored as a view until an insert. What's more, you can set the DB to revert tables to virtual when they are empty.

I suspect since IBM is pushing to get folks to convert from Oracle, especially running SAP with anywhere from 50-100,000 tables that they can save space as two of the big sellers are that it saves space and overhead by virtual tables and compression at table level.

I've found lots of subtle bugs with this on SAP systems including issues where it fails to create the tables properly, thinks it has but it hasn't and other nifty little problems where the application dumps and leave no trace of what it failed on.

Tue Feb 15, 10:30:00 PM EST  

Blogger Rick Bruns said....

Another potential gotcha is if you query dba_segments to determine if a tablespace is empty when wanting to drop the tablespace, you could drop objects that you didn't realize were defined with that tablespace since you wouldn't see them with the dba_segments query.

Mon Jul 07, 04:50:00 PM EDT  


<< Home