RE: multiple extents are OK, dagnabbit!

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" <Kurt.Wiegand_at_CWUSA.COM>@fatcity.com on 01/24/2002 06:45:17 AM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: 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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Rajesh.Rao_at_chase.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).