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
Averell
Messages: 17
Registered: November 2006
Junior Member
Dear,

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

http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

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


select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE, EXTENT_MANAGEMENT, ALLOCATION_TYPE 
from user_tablespaces

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

Report message to a moderator

Previous Topic: dbms_shared_pool.keep auditing
Next Topic: pls help me sovle this problem "Xlib:connection to ":0.0" refused by server"
Goto Forum:
  


Current Time: Fri Dec 13 06:18:05 CST 2024