Oracle-L: RE: Limited SORT_AREA

From: Arun Chakrapani <ArunC_at_1800FLOWERS.com>
Date: Tue, 28 May 2002 10:04:42 -0800
Message-ID: <F001.0046CFA6.20020528100442@fatcity.com>

Are u getting that error quite frequently

-----Original Message-----
Sent: Tuesday, May 28, 2002 1:52 PM
To: Multiple recipients of list ORACLE-L

Arun - Thanks for the tip of using ALTER SESSION. I'll try that next time, and I hope next time isn't too soon.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Tuesday, May 28, 2002 12:32 PM
To: Multiple recipients of list ORACLE-L

Whenever I create or rebuild indexes i have been having the sort_Area_size as 1 gig,I have never faced any issues,but one thing i want to know why did u have to bounce the instance for this,whenever i create i add the sort area size for the session only and not on the instance level. we were building indexes on large tables which has around 50 to 60 million rows with their avg row size of 300k.I open up 6 to seven sessions and have all their sort_Area_Size to 1 gig and create the indexes. But we have 23 gigs of ram and 60 gigs of swap space and it has 23 cpus. I am surprised about this error which u got.

-----Original Message-----
Sent: Tuesday, May 28, 2002 12:54 PM
To: Multiple recipients of list 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: Arun Chakrapani
INET: ArunC_at_1800FLOWERS.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: Limited SORT_AREA_SIZE