To: Reynels Lee
On Thu, 28 Oct 1999
Subject: ORA-1630: Max Extents Reached
> Hi Ari Kaplan,
>
> I currently facing a problem of this msg :
>
> ORA-01630: max # extents (121) reached in temp segment in tablespace
> TEMP
>
> I hope you can provide me some info of, how should I approach this
> problem in order to solve it?
>
> Thanks and really appreciate your help!
>
> Rey
>
What you need to do is increase the default INIT and NEXT in the TEMP
tablespace:
ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 1M NEXT 10M);
When you do a SORT, ORDER BY, GROUP BY, MAX, MIN, and other functions,
Oracle uses memory (sort_area_size) to do the calculation. When the memory
is used up, Oracle creates temporary segments in the TEMPORARY TABLESPACE
default for the user running the operation. In your case it is TEMP.
These temporary segments use the default storage clauses for TEMP and in
your case it is too small, resulting in 121 extents. This is the default
limit for your database, based on the database block size.
Change the default storage for TEMP with the above syntax (or different
INIT/NEXT numbers) and you will be set.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 335+ Oracle tips, visit my Web Page: <->
<-> <->
<-> http://www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page