RE: Running out of extents on temporary tablespace

Darren,
It appears that the TEMP extent are sized too small. The maxexents can be raised to 'unlimited'. However, I would first try increasing the initial (= next ) extent size in for the TEMP tablespace. Ideally it should be some higher multiple of the sort_area_size. Raise sort_area_size only if there is enough free memory left on the server and it will accomodate new memory demand by all the sorts that may take place. But resizing temp extents to larger size would help.

HTH, Regards,

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Browett, Darren [SMTP:dbrowett_at_city.coquitlam.bc.ca]
> Sent: Friday, July 27, 2001 4:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Running out of extents on temporary tablespace
>
> Oracle 8.0.5.1.1
>
> I am getting the ORA-01630 max number of extents (505) for a temp segment
> in
> my temporary tablespace
> message when I do a select count(*)
>
> I have two tables,
>
> 1. has 300,000+ rows
> 2. has 1,200,000 rows
>
> I retrieved the numbers by performing a select count(*) from xxxxxx.
>
> I have a view that unions the two tables together for reporting purposes.
> If
> I try to do a select count(*) on the view, I run out of extents. Also on
> occasion
> when we rebuild table 2, I also run out of extents. (ORA-01630)
>
> I am assuming that it is the sort_area_size parameter that may be causing
> the problem,
> but I am not sure. The temp tablespace and parameter values are as
> follows
> :
>
> initial extent = 524288
> next extent = 524288
> sort_area_size = 524288.
>
>
> Checking the sysstat table I receive the following, which according to
> some
> of the books I
> have read indicate whether or not there is an issue with the
> sort_area_size
> parameter.
>
> NAME VALUE
> ------------------------------ ----------
> sorts (memory) 35,128
> sorts (disk) 155
>
> Another tips, comments or looking in a different area to resolve this
> would
> be greatly
> appreciated
>
> Thanks
>
> Darren
>
> --------------------------------------------------------------------------
> --
> -------------------------------------------------
> Darren Browett P.Eng This message was
> transmitted
> Systems Admin/DBA using 100% recycled
> electrons
> Information and Communications Technology.
> City of Coquitlam
> P:(604) 927 - 3614
> E:dbrowett_at_city.coquitlam.bc.ca
> --------------------------------------------------------------------------
> --
> -------------------------------------------------
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Browett, Darren
> INET: dbrowett_at_city.coquitlam.bc.ca
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).