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