Re: Help: oracle space problem. Error code:ORA-01652

If the tablespace was created and designated as TEMPORARY, adding additional data files is not the answer. I'm not sure, but I believe the intent of temporary tablespace was that Oracle would not have to create, drop, and recreate temporary segments as often in a temporary tablespace.

I had to re-drop and recreated the temporary tablespace with a larger size (this solved the problem).

John D. Hoang
Innovative Solutions Consulting, Inc.
jhoang_at_mindspring.com

In article <883805638.1121189894_at_dejanews.com>,   streak_at_iag.net wrote:
>
> Is the tablespace your temporary tablespace? If not, then you may need to
> designate a temporary tablespace for the user... you don't want your temp
> sorting being done in a tablespace other than your temporary tablespace.
>
> Assuming that the tablespace in the error is the temporary tablespace you
> may very well need to add additional space to the tablespace. However,
> there are some additional things you may also want to do.
>
> SELECT INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE FROM DBA_TABLESPACES
> WHERE TABLESPACE_NAME = 'tablespace_name';
>
> PCTINCREASE for your temp tablespace should be set to 0; if it is 50(the
> default) each additional extent that is allocated is 50% larger than the
> last. As each extent becomes larger in size, Oracle must be able to find
> contiguous free space to satisfy the extent allocation. In addition,
> this leads to fragmentation.
>
> Your INITIAL_EXTENT and NEXT_EXTENT should also be the same size.
>
> You should also check the value of the SORT_AREA_SIZE (in the INIT.ORA).
> It defaults to 64k which is too small. Increasing this parameter will
> assist in the demands that you place on the temp tablespace. I can't
> really tell you what you should set this at, because it is contingent
> upon the environment in which you are operating and the number of
> concurrent users.
>
> The INTIAL_EXTENT and NEXT_EXTENT size should be greater than your
> SORT_AREA_SIZE. I have read that the extent size should be 1.5 to 2.5
> the size of the SORT_AREA_SIZE.
>
> Hope that helps,
> Shawn
>
> In article <68d5c6$qs8$1_at_news.worldonline.nl>,
> "Wouter Hazenberg" <@worldonline.nl> wrote:
> >
> > Mary,
> > The tablespace you use for temporary segments is probably still to small.
> > After increasing the tablespace the query creates more extents in the
> > temporary tablespace, but not enough. The size of every next extent will
> > increase, that's why it askes for bigger and bigger extents. If the
> > tablespace is used for temporary segments only, i would enlarge the
> > tablespace again, or if possible, recreate it in one extent.
> > If you use a tablespace that also includes data (temporary tablespaces are
> > usually empty)
> > i would create a tablespace TEMP, and assign it to your user-ID :
> > alter user mary temporary tablespace temp;
> >
> > good luck,
> > Wouter
> >
> > hchen_at_cc.memphis.edu heeft geschreven in bericht
> > <1997Dec30.191027_at_latte.memphis.edu>...
> > >I was running a big query again Oracle 7.3.3 at NT server. I got error
 message
> > >says:
> > >
> > >"ORA-01652: unable to extend temp segment by 315 in tablespace SELMC"
> > >*SELMC is the name of tablespace.
> > >
> > >I told the DBA and he increased the segment space for me. I run the same
 query
> > >again, I got the same error, but it said unable to extend temp segment by
 417.
> > >Then the DBA drop the old segment and created new temp segment with bigger
> > >size, but when I run the query again, I still got the same error code and
 now
> > >it said unable to extend temp segment by 1595. Then I tried disconnect to
 the
> > >database and then run the same query, unfortunately, I still got the same
 error
> > >code and now it is said unable to extend temp segment by 5120!
> > >
> > >Our tablespace is 175 meg, and used up 82 meg. The roll-up segment is 30
 meg.
> > >There are about 90,000 records in the fact table.
> > >
> > >My questions are:
> > >1. 1.How to deal with this error code?
> > >2. 2.Why I still get the same error after the DBA increased the space?
> > >3. 3.Why the unable to extend temp space become bigger and bigger after
 each
> > >query?
> > >
> > >I am new to Oracle, Please help. Any suggestion are welcome and
 appreciated!
> > >You can either reply this mail or send reply to mchen_at_netbasecomp.com.
> > >Thank you!
> > >
> > >Mary Chen.
> > >
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet