ora_01631max # of extents nnn reached in table table-name

[Ablakios]

Hi all
One of our user had the following error loading data in a table in his own schema.

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

We are running Oracle 8.1.7.4 on Windows advanced server with db_block_size of 8192.


According to the documentation the ideal solution is to Increase the maximum number of extents allowed for the table, i.e:

For a 8K block size an object could have no more than 505 extents

alter table storage (maxextents 500);

When I query the database as follows

SELECT max_extents , tablespace_name
FROM dba_tables
WHERE table_name = 'USERS' AND owner = 'ower_name';

MAX_EXTENTS TABLESPACE_NAME
2147483645 USERS

Why I am getting 2147483645 for max_extents instead of 505 FOR 8K block size

Can some one explain that?

[engiri]

select next_extent from dba_tables where table_name=;

[jmodic]

Originally posted by Ablakios
According to the documentation .....
For a 8K block size an object could have no more than 505 extents

Which documentation are you reading? Those limits of maxextents based on db block size are ancient history, from times of earlier releases of Oracle7!

The upper limit for maxextents is UNLIMITED, and this "unlimited value" in the database is represented by that large number which you are getting (2147483645).

[Ablakios]

Thanks for the update Jmodic
Do you think
alter table table_name storage (maxextents unlimited); will do the trick
or
alter table table_name storage (maxextents integer_closed to_ unlimited);

is there any repercusion for specifying UNLIMITED instead of a value closed to 2147483645?

[abhaysk]

Originally posted by Ablakios

alter table table_name storage (maxextents unlimited); will do the trick

Will do