ORA-01631

I wrote this post for a colleague who wants to do DBA work but hasn't had any training yet. I tested it on an Oracle 9 database. When you create a table, you can include certain options. In the example shown, a table is created with an initial setting of 10 kilobytes. This specifies the size of the table's first extent. It has a next setting of 10 kilobytes too. This gives the size of subsequent extents. The maxextents value of 10 means that the table can have no more than 10 extents:

SQL> create table storage_test

  2  storage (initial 10k next 10k maxextents 10)

  3  as select * from dba_tables where 1 = 2

  4  /

 

Table created.

SQL>
  

Now add some data to the table. This works:

 

SQL> insert into storage_test

  2  select * from dba_tables

  3  /

 

826 rows created.

SQL>
  

Then try adding some more. This fails as the table has reached its maxextents setting:

 

SQL> insert into storage_test

  2  select * from dba_tables

  3  /

insert into storage_test

            *

ERROR at line 1:

ORA-01631: max # extents (10) reached in table

ORACLE.STORAGE_TEST

SQL>
  

One solution is to increase the maxextents value for the table:

 

SQL> alter table storage_test

  2  storage (maxextents 20)

  3  /

 

Table altered.

SQL>
  

Then when you try the insert again, it works:

 

SQL> insert into storage_test

  2  select * from storage_test

  3  /

 

826 rows created.

 

SQL>