| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Limited SORT_AREA_SIZE
Arun - No, I just needed to rebuild our largest table this weekend and
re-create the indexes afterward. Fortunately with SORT_AREA_SIZE at
150-meg., the indexes build in a reasonable amount of time, so it was merely
an inconvenience. I was able to enjoy one day of the weekend. I just
wondered for next time I do this. Since I only perform this task once every
couple of years, I'll probably forget most of the details before I do it
again. I just wondered if anyone had a reason why I couldn't go past
150-meg. But I appreciate your suggestion because there are obviously many
times when bouncing the instance is inconvenient. But that is the best part
of this list, the opportunity to get ideas from the great minds. Most days
it is pretty humbling.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Tuesday, May 28, 2002 1:05 PM
To: Multiple recipients of list ORACLE-L
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).
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).
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).
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).
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 - 13:30:23 CDT
 |
 |