Home » RDBMS Server » Server Administration » ORA-01684: max # extents (30) reached in table ...
ORA-01684: max # extents (30) reached in table ... [message #202263] Wed, 08 November 2006 21:56 Go to next message
Messages: 17
Registered: November 2006
Junior Member

I just truncated a table, and trying to re-populate data (inserting record by record). Before truncate, that table had more than 3 million records.

Then, I got the error message:"ORA-01684: max # extents (20) reached in table USER_NAME.TABLE_NAME" after more than 20 thousand records inserted.

I tried "Alter table USER_NAME.TABLE_NAME storage (maxextents 30)". Then, I was able to insert about 10-15K records more. Now I get the error "ORA-01684: max # extents (30) reached in table USER_NAME.TABLE_NAME".

I'm thinking of extending the table again (to 40 or might be to unlimited), but I don't know why this happened. Is there any other reasons? Because, before truncating, I had more than 3M records there. All records are at the same size.

I tried the sql: "Select initial_extent, next_extent, min_extents, max_extents from all_tables where table_name = 'TABLE_NAME';" All those values are null.

Please help me! Thank you very much!

[Updated on: Thu, 09 November 2006 08:25]

Report message to a moderator

Re: ORA-01684: max # extents (30) reached in table ... [message #202442 is a reply to message #202263] Thu, 09 November 2006 18:34 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
global temporary tables have blank values like you describe - not sure if that your situation or not.

If your table doesn't have initial/next extents specified, it'll get the extent size from the tablespace it resides in. There can be many reasons why it worked before but not now including:
changes in pctfree setting on the table (default 10 %)
import/export using compress extents
rebuilding the table (in same or different tablespace)
import/export a table originally created in dictionary managed TS into a locally managed TS
upgrade a dictionary managed to a locally managed TS

In any case - there's no need to be concerned with large numbers of extents - set it up to the 1000's


Make sure that you aren't somehow inserting trailing spaces in your data. vsize shows the number of bytes used per field.
select avg(vsize(ename)), avg(vsize(sal) from emp

from user_tablespaces

[Updated on: Thu, 09 November 2006 18:36]

Report message to a moderator

Previous Topic: Reduced Backup as tersting instance
Next Topic: OEM issue
Goto Forum:

Current Time: Wed Aug 23 06:48:35 CDT 2017

Total time taken to generate the page: 0.01712 seconds