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" <Kurt.Wiegand_at_CWUSA.COM> 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: jwilton_at_speakeasy.net
>
> 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: Wiegand, Kurt
> INET: Kurt.Wiegand_at_CWUSA.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).
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: abardeen1_at_yahoo.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).
Received on Thu Jan 24 2002 - 09:04:03 CST
