Running out of extents on temporary tablespace

From: Browett, Darren <dbrowett_at_city.coquitlam.bc.ca>
Date: Fri, 27 Jul 2001 13:15:15 -0700
Message-ID: <F001.00357ECE.20010727133125@fatcity.com>

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,

has 300,000+ rows
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). Received on Fri Jul 27 2001 - 15:15:15 CDT