Re: drop tabblespace XXX including contents

In article 2281_at_psrc.isac.co.jp, kabata_at_psrc.isac.co.jp (Takeshi Kabata) writes:
>
> 3 weeks ago, I try to drop tablespace on oracle V6. But it spent very long
> time ( about 4days ).
>
> That tablespace is :
>
> size : 12GB
> tables : over 500
> indexes : over 1500
>
> Does anyone know how to drop tablespace fast ? Any help or ideas would be
> most appreciated.
>
> = o `OO kabata_at_isac.co.jp
> ---/- (VV) extention #65
> = / / || 03-3406-1645
> ~~~~~ ＾＾

It is my understanding that this is a "KNOWN" Oracle bug, not being able to use the INCLUDING CONTENTS qualifier. I beleive that V7 fixes this but I cannot confirm it as we don't have it installed yet.

About the best you can do with dropping a tablespace is to get a list of tablenames from SYS.DBA_TABLES, using SELECT * FROM SYS.DBA_TABLES WHERE TABLESPACE_NAME = tsname; . Unfortunately this only gets Table objects, and you're on your own to find sequences and others.

I'd be very interested in finding out how others get around this bug, as well!

---

--------------------------------+------------------------+--------------------
Gary B. Eckhardt                |  Software Development  |  This space for
Computer Sciences Corporation   |  MISTS HSC Support     |  rent or lease.
eckhardg_at_radon.brooks.af.mil    |  (210) 536-2826        |  (Cheap!)

   "If the world was a logical place, all cowboys would ride side-saddle."
                         --Ranger Doug, "The Riders In The Sky"