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 Received on Mon Nov 06 2000 - 13:53:01 CST