Re: A tablespace with 400,000 free extents

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote in 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

I have a brainstorm which might be useful, or then again it might not be worth the electrons used to compose it.

1. Export the data.
2. Create a NEW_TABLE with much more appropriately sized extents.
3. Import the data into NEW_TABLE
4. RENAME OLD_TABLE to BAD_TABLE;
5. RENAME NEW_TABLE to OLD_TABLE;

Have the BAD_TABLE go away at your leisure.

In any case I hope you get my general idea.

HTH & YMMV, HAND! Received on Sun Jul 21 2002 - 19:25:26 CDT