Search

Top 60 Oracle Blogs

Recent comments

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
Connected.

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
Connected.

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...