Cry about...
Oracle Troubleshooting

---

ORA-01631: max # of extents nnn reached in table xxxx

---

Symptom:

The error:

ORA-01631: max # of extents nnn reached in table <table-name>

Cause:

For each table a maximum number of extents can be specified. This error means that in order to accommodate some new data Oracle needed to extend a table but could not because the maximum number of extents have been reached. The error reports back both the current (maximum) number of extents and the table name.

This error is often also symptomatic of the next extent size being too small.

Remedy:

Increase the maximum number of extents allowed for the table, i.e:

alter table <table-name> storage (maxextents 500);

or alternatly remove the limit on the number of extents, i.e.:

alter table <table-name> storage (maxextents unlimited);

and try the operation again. It is probably also worth reviewing the size of the next extent, i.e.:

select next_extent from dba_tables where table_name=<table_name>;

If the next extent size is ‘small’ (when considering the quantity of data to be stored) then consider increasing it.

---

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.