| ORA-01684: max # extents (30) reached in table ... [message #202263] |
Wed, 08 November 2006 21:56  |
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  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
