RE: Max Extents Question

Not trying to scare you but the number of extents is unusually high. Maybe it's just reported wrong from the data dictionary? Perhaps, you data dictionary is corrupted. Just my guess as far as how in the world can you end up with this number.

Richard Ji

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jared Still Sent: Monday, November 06, 2000 2:55 PM
To: Multiple recipients of list ORACLE-L Subject: Re: Max Extents Question

On Mon, 6 Nov 2000, Kan Man wrote:

> Hello All.,
>
> We have a table A which has reached out max extents(2147483645)..One of
> our collegaues is saying that Oracle advise not to increase extents beyond
> this limit...
>
> I basically have two questions
> 1.Has Oracle advised for a maximum limit for max extents..?
> 2.If yes, what can we think of as a solution..?
>

First of all, I'm going to question your claimed number of extents, which you say is 2,147,483,645. Are you sure about that?

If you have a minimum block size of even 2k, that would put this table at 4 Terabytes.

Do you really have a 4 TB table?

OK, let's assume you do.

If you are not using locally managed tablespaces, you have a real problem.

With data dictionary managed extents, you only have one option.

You will have to export the entire database and rebuild it.

Why?

Because the recursive SQL that will be invoked from trying to drop a table with 2 billion+ extents will never finish running in your lifetime.

If you are using LMT's, then you may be able to drop just this table and import it.

I'm really curious about the number of extents in this table.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address jkstill_at_teleport.com - private

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