Re: Question: Safest way to 'recreate' a tablespace

"BD" <bobby_dread_at_hotmail.com> wrote in message news:1130347512.148010.95400_at_g44g2000cwa.googlegroups.com...
> Hi, all.
>
> I have a situation where a file was added to a tablepace incorrectly.
> (9.2.0.6 on AIX 5.2)
>
> For the purposes of tidiness, I want to remove it. I gather that this
> will generally mean either shrinking the unwanted file down to a tiny
> size so it will have no effect on the space usage, or re-creating the
> tablespace
>
> I have read options in the group about moving segments, and also about
> exporting and importing.
>
> Assuming I want to keep the same tablespace name, I'm kind of thinking
> that the best process would be:
>
> -Export tablespace
> -Drop tablespace
> -Delete unwanted files
> -Create tablespace with the correct file allocation
> -Import tablespace
>
> Is this still the best/safest route to take?
>
> Comments welcome.
>
> Thanks!
>
> BD
>

I made those "mistakes" in the past too. But I never "cleaned it up", since this involves too much work (and the risk of forgetting something like indexes or constraints). I just would shrink the datafile to a small size, or, if it's on the wrong filesystem, rename and move it. Maybe someday you will need the extra space after all.

Matthias Received on Wed Oct 26 2005 - 14:25:18 CDT