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:[EMAIL PROTECTED]]
> 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:[EMAIL PROTECTED]
> --------------------------------------------------------------------------
> --
> -------------------------------------------------
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Browett, Darren
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
