Min Extents on Locally Managed tablespaces


A "worthy of note" (which is hidden in the documentation) that comes as a little surprise if you encounter it by chance. In a locally managed tablespace, the MINEXTENTS storage clause is "ignored" in the sense that it is just used to determine an appropriate initial extent, after which the MIN EXTENT clause is reset to 1

Consider the following example (on a 1M uniform locally managed tablespace)

SQL> create table DUMMY storage ( MINEXTENTS 4);

 Table created.

SQL> select initial_extent, min_extents
  2  from user_segments
  3  where segment_name = 'DUMMY';

INITIAL_EXTENT   MIN_EXTENTS
--------------   -----------
       4194304             1