On the topic, I once had a tablespace with 300,000+ free extents and 0 used
extents. We executed a drop tablespace command, and looking at fet$ and the
rate at which it was dropping extents from the table, we estimated it would
take us 64 hours. This was on a 7.3.4 db, and we thought it better to trash
the database, and recreate. That was much much faster ;-)
I once remember reading an artice at Jonathan Lewis site, which basically
talked of stopping smon from coalescing, deleting all the rows from fet$
and adding one row for all the free extents. Of course, this was not
supported by Oracle. Did anyone from this list ever really try that? Just
curious.
Raj
"Wiegand, Kurt" <[EMAIL PROTECTED]>@fatcity.com on 01/24/2002 06:45:17
AM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
