Limited SORT_AREA_SIZE

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 28 May 2002 08:53:40 -0800
Message-ID: <F001.0046CE71.20020528085340@fatcity.com>

This weekend I was rebuilding a large table and when it came time to rebuild the indexes, I cranked the SORT_AREA_SIZE up to 1/2 gig. It bombed off with an ORA-04030. I kept reducing the SORT_AREA_SIZE (bouncing the instance to make the change) until I got it down to 150-meg. Then everything ran fine. I discussed this with my Unix system administrator and he felt everything was fine from his end. I ran Oracle's maxmem utility and received:

oracle.fin7>maxmem

Memory starts at: 5369596416 (1400d8a00)
Memory ends at:   6399795195 (17d751ffb)
Memory available: 1030198779 (3d6795fb)   

So it seems like I could have SORT_AREA_SIZE much higher than 150-meg. The indexes built fine, but I am curious about the problem. Any ideas?

Oracle 8.1.6 (until Oracle gets a good 8.1.7 version) Compaq Tru64 4.0E
4-cpu.
4-gig. of system memory.
shared_pool_size = 400-meg
This is the only instance on this server.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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). Received on Tue May 28 2002 - 11:53:40 CDT