Re: Limited SORT_AREA_SIZE

There are limits to amount of memory a process can attach to. I have hit that wall with the size of the SGA from time to time. I would think that limit would be associated with the sort area also, sort_area + sga if you will. Take a look at document
http://metalink.oracle.com/metalink/plsql/ml2_documents.showNot?p_id=1028623.6&p_font

 or just do a search on "sgabeg".

Brian P. MacLean
Oracle DBA, OCP8i

                                                                                                                                       
                      DENNIS WILLIAMS                                                                                                  
                      <DWILLIAMS_at_LIFETO        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                   
                      UCH.COM>                 cc:                                                                                     
                      Sent by:                 Subject:  Limited SORT_AREA_SIZE                                                        
                      root_at_fatcity.com                                                                                                 
                                                                                                                                       
                                                                                                                                       
                      05/28/02 09:53 AM                                                                                                
                      Please respond to                                                                                                
                      ORACLE-L                                                                                                         
                                                                                                                                       
                                                                                                                                       

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).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Brian_P_MacLean_at_eFunds.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).