Initial Extent for a table gets allocated only after data insertion in Locally managed user tablespace

Question

In case of Locally managed SYSTEM tablespace, when objects are created in a user tablespace (which is also locally managed) initial extents are getting allocated only after data insertion.

Whereas in the same scenario where SYSTEM tablespace is dictionary managed, initial extent gets allocated whenever table is created.

create tablespace lmt datafile 'df.f' size 5M extent management local;

conn scott/tiger

create table sample (id nuber) tablespace lmt;

select * from user_tables where table_name = 'SAMPLE'; 

1. initial extent is null when SYSTEM tablespace is Locally managed
2. initial extent has value when SYSTEM tablespace is dictionary managed

After data insertion, initial extent is allocated in case (1)

Is this the expected behavior? because as per Oracle docs, 'Oracle allocates space for initial extent when you create the schema object'

Answer 1

I think you're quoting (or rather, slightly paraphrasing) this part of the documentation:

INITIAL

Specify the size of the first extent of the object. Oracle allocates space for this extent when you create the schema object. ...

That isn't the whole story though. Tables can be created with deferred segment creation:

deferred_segment_creation

Use this clause to determine when the database should create the segment(s) for this table:

• SEGMENT CREATION DEFERRED: This clause defers creation of the table segment — as well as segments for any LOB columns of the table, any indexes created implicitly as part of table creation, and any indexes subsequently explicitly created on the table — until the first row of data is inserted into the table. ...

• SEGMENT CREATION IMMEDIATE: The table segment is created as part of this CREATE TABLE statement.

It looks like what you are seeing is nothing to do with the SYSTEM tablespace being locally managed, as you suspected, and that is just a coincidence. The difference is the default for segment creation, which is controlled by an initialisation parameter, deferred_segment_creation. From what you've shown that is set to TRUE in the database that has the locally-managed SYSTEM tablespace, and FALSE in the one that has that dictionary-managed.

You can get consistent behaviour by overriding the default, either to defer creation:

create table sample (id number) segment creation deferred tablespace lmt;

Table SAMPLE created.

set null "(null)"
select initial_extent from user_tables where table_name = 'SAMPLE';

INITIAL_EXTENT
--------------
(null)              

or to create immediately:

create table sample (id number) segment creation immediate tablespace lmt;

Table SAMPLE created.

select initial_extent from user_tables where table_name = 'SAMPLE';

INITIAL_EXTENT
--------------
         65536

Or you could change the initialisation parameters to be the same, of course, but that's more work and might affect other code that assumes the current behaviour.