Minextents and Maxextents

This post looks at the values you are allowed to use for these 2 parameters. Minextents specifies the minimum number of extents your table is allowed to have so zero is not an option!

SQL> create table storage_test1
  2  (col1 number)
  3  storage (minextents 0 maxextents 10)
  4  /
storage (minextents 0 maxextents 10)
                      *
ERROR at line 3:
ORA-02220: invalid MINEXTENTS storage option value

SQL>

When you have created a table, you can look in user_extents to see how many extents it has. This will usually be equal to the minextents value you provided:

SQL> create table storage_test2
  2  (col1 number)
  3  storage (minextents 1 maxextents 10)
  4  /

Table created.

SQL> select count(*) from user_extents
  2  where segment_name = 'STORAGE_TEST2'
  3  /

  COUNT(*)
----------
         1

SQL>

Maxextents specifies the maximum number of extents your table is allowed to have. It cannot be less than minextents for obvious reasons:

SQL> create table storage_test3
  2  (col1 number)
  3  storage (minextents 4 maxextents 3)
  4  /
storage (minextents 4 maxextents 3)
                                  *
ERROR at line 3:
ORA-02221: invalid MAXEXTENTS storage option value

SQL>

In the next example, minextents is set to 5 and the table ends up with 5 extents as you might expect. You can specify maxextents as unlimited then look in user_tables to see what this really means:

SQL> create table storage_test4
  2  (col1 number)
  3  storage (minextents 5 maxextents unlimited)
  4  /

Table created.

SQL> select count(*) from user_extents
  2  where segment_name = 'STORAGE_TEST4'
  3  /

  COUNT(*)
----------
         5

SQL> select max_extents from user_tables
  2  where table_name = 'STORAGE_TEST4'
  3  /

MAX_EXTENTS
-----------
 2147483645

SQL>

So now you know what unlimited really means, try setting maxextents to that value + 1. This fails, which is not surprising:

SQL> alter table storage_test4 storage
  2  (maxextents 2147483646)
  3  /
(maxextents 2147483646)
            *
ERROR at line 2:
ORA-02221: invalid MAXEXTENTS storage option value

SQL>