Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A tablespace with 400,000 free extents

Re: A tablespace with 400,000 free extents

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sun, 21 Jul 2002 17:30:09 +1000
Message-ID: <4jt_8.40116$Hj3.120380@newsfeeds.bigpond.com>


Hi Vsevolod,

What you need to do is fix the problem permanently. And yes, it might take some time initially but once completed you can kiss these issues goodbye.

Unfortunately the alter database datafile name offline drop is only effective if your database is in noarchivelog mode and you want to take a tablespace offline. To get rid of the tablespace permanently still requires you to drop the tablespace.

Therefore we want to start moving into locally managed tablespace territory (assuming you're on 8i or above).

You could convert the tablespace to locally managed via dbms_space_admin.tablespace_migrate_to_local, but this appears to leave your tablespace in a fragmented state and choosing an appropriate unit size is a pain.

Therefore I recommend what I recommended. After the data has been migrated to the new tablespace (using one of the methods I previously suggested), drop the old empty TS once and for all.

No longer will you have fragmentation problems, no longer do you have to wait eons to reorg your tables. I suggest with the LMT though that you still pick an appropriate uniform size so extent numbers don't end up in the 10s of thousands (there may, there may not be issues with this).

I'm not sure why a transport tablespace will hang if another tablespace is in the middle of being coalesced. It might have something to do with locks in the DD as both operations are concurrently being performed. But with LMT, you will not have to worry about coalescing again.

Regards

Richard
"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message news:4f7d504c.0207201751.215e270c_at_posting.google.com...
> Richard,
>
> Thanks
>
> But the DROP TABLESPACE command will be as slow as
> ALTER TABLESPACE COALESCE.
>
> My problem is to find a way to get rid of this tablespace
> in a reasonable amount of time. I had a table in this
> tablespace, it has 80,000 extents. I dropped it - the
> DROP TABLE took 16 hours to run. From this, my estimate
> for DROP TABLESPACE (or ALTER TABLESPACE COALESCE) is 80 hours.
>
> Another question is why ALTER TABLESPACE COALESCE stops
> IMP from plugging in tablespaces?
>
> Regards,
> Sev
Received on Sun Jul 21 2002 - 02:30:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US