Kurt, If you're on 8.1.6.3, 8.1.7.0.0 or 8.1.7.1.0 this sounds suspiciouslly like either bug 1640583 or bug 1397603, both of which are fixed in 8.1.7.2+
The workaround for bug 1397603 is to set _db_handles_cached = 0 in the init.ora. HTH, -- Anita --- "Wiegand, Kurt" <[EMAIL PROTECTED]> wrote: > Sorry Jeremiah, I don't have a clue....... > > got the same error (after the same 2 hours) after > purging the > shared pool; there was no activity at all on the > database, so > I thought about increasing the size of the shared > pool (~10.5MB) > but had a need, and the option, of simply replacing > the database > with a backup. > > SQL> alter tablespace USR2 coalesce > * > ERROR at line 1: > ORA-04031: unable to allocate 4180 bytes of shared > memory ("shared > pool","unknown object","sga heap","state objects") > > > > -----Original Message----- > Sent: Wednesday, January 23, 2002 11:56 AM > To: Multiple recipients of list ORACLE-L > > > On Tue, 22 Jan 2002, Wiegand, Kurt wrote: > > > sort of on the subject.....I once had a table with > ~88000 extents > > (most 1 block!) it took 8 hours to delete and a > subsequent coalesce > > ran for 2 hours before failing as it ran out of > shared > > memory....(8.1.5)..... > > Kurt, > > What component of the SGA becomes exhausted by a > long-running > coalesce? > > -- > Jeremiah Wilton > http://www.speakeasy.net/~jwilton > > > -----Original Message----- > > Sent: Tuesday, January 22, 2002 11:55 AM > > To: Multiple recipients of list ORACLE-L > > > > The problem arose in the catalog upgrade script. > It would never > > return. My diary says we let one attempt run for > 36 hours. The process > > showed CPU usage and I/O but nothing happened. > Some of the Oracle guys > > figured the problem was with the $fet (or whatever > tables hold the > > extent > > info, I never bother with the internals of the > data dictionary) having > > problems while being restructured. Once the tables > were changed from 40K > > to 500M > > extents the upgrade took less than 2 hours. > > > > One of the suggestions I did not use was to edit > sql.bsq to provide much > > larger > > extents for the table holding the extent info. > Even though I do this for > > the SOURCE$ table I am a big fan of the KISS > principle and rebuilding > > the tables > > needed to be done anyways. > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jeremiah Wilton > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Wiegand, Kurt > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). __________________________________________________ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).