Wednesday, November 09, 2011

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>

No comments: