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>

Truncate (Part 1)

This post shows the effect of the TRUNCATE statement. First create a table:

SQL> create table truncate_example
  2  as select * from dba_tables
  3  /

Table created.

SQL>

Count the lines in the table:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
      3023

SQL>

And the number of extents:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Truncate the table with the REUSE STORAGE option:

SQL> truncate table truncate_example reuse storage
  2  /

Table truncated.

SQL>

Now count the number of rows in the table. There will be none:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Truncate does not produce any undo information so if you do a rollback, the deleted lines will not reappear:

SQL> rollback;

Rollback complete.

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Now count the number of extents. It will be the same as before because you included the REUSE STORAGE clause:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Now reinsert the deleted rows from the source table:

SQL> insert into truncate_example
  2  select * from dba_tables
  3  /

3024 rows created.

SQL>

Truncate the table again but this time, add the DROP STORAGE clause, which is the default:

SQL> truncate table truncate_example
  2  drop storage
  3  /

Table truncated.

SQL>

Count the number of rows in the table again. There will be none, as before:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

And count the number of extents. By adding the DROP STORAGE clause, the table’s extents have been deallocated. The number of extents remaining is determined by the table’s MINEXTENTS value:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

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

SQL>