Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help with temp tablespace problem

Re: Help with temp tablespace problem

From: ClayD <claydavidson_at_earthlink.nospam.net>
Date: Sun, 26 Aug 2001 04:52:40 GMT
Message-ID: <sg%h7.4125$434.463488@newsread2.prod.itd.earthlink.net>


An example:

Alter tablespace yourtemptablespace

    default storage ( initial 2M next 2M);

You may also consider making the maxextents parameter of the storage clause unlimited just in case.
You may also want to increase the size of your sort_area_size for your session if feasible.

What is happening is that you are trying to order the result set. This requires the query to be sorted.
It will try and sort in memory ( sort_area_size) and when it runs out, it will have to go to disk
It will start using the temp tablepspace to sort and will create new extents as needed.
Since the table is so large, my guess is that the initial default extents for the tablespace are small and you max out on the number allowed. The default number is usually determined by the database block size. You can increase the number allowed ( maxextents) and get a bunch of small ones or you can make each one larger ( initial and next).

cheers,

clay

"Stan Brown" <stanb_at_panix.com> wrote in message news:9kpd55$hgm$1_at_panix3.panix.com...
> I'm trying to run a slect that involves an ORDER BY on a table with over 1
> million rows.
>
> I originaly got an error message about not being able to extend the
TEMP_TS
> tablespace. So I looked around and found a 500MB tablespace that I had
> created for a test a while back, and that has since been completly emptied
> out. I did a ALTER USER to set the user I was runig as temporary tablespce
> to this tablespace, and thought everything would be fine. Unfortunately,
> now I am geting this error message:
>
> ORA-01630: max # extents (121) reached in temp segment in tablespace
GNUMETRICS_TS
>
> What do I need to do to allow the use of larger temporary segments in this
> new tablespace?
>
> Oracle 7.3.4, if it matters.
>
>
>
Received on Sat Aug 25 2001 - 23:52:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US