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

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