Re: How to coalesce an LMT TS to drop extra datafiles?

From: Nuno Souto <dbvision_at_iinet.net.au>
Date: Sat, 02 Feb 2013 17:03:33 +1100
Message-ID: <510CAC35.1050904_at_iinet.net.au>



Hans Forbrich wrote,on my timestamp of 2/02/2013 3:11 AM:
> On 01/02/2013 8:37 AM, Wolfgang Breitling wrote:
>>> Provided you have the space ( temporarily ) and no long columns, creating a new tablespace, moving all segments from the old to the new ( lobs need to be moved on their own ) and dropping the old. Optionally renaming the new to the old if tablespace names are important.
>>>
> I second that: Alter Table Move ...
>
> With the added advantage of being able to review which indexes are still
> required. Why move/rebuild them if they are no longer in use?
>
> "Why do we have that index?"
>
> "Well, we needed it for that report."
>
> "But we stopped using that report 8 months ago."

Love it! I do that too, quite often. Particularly with partitioned tables. In most instances in our DW it's actually faster to partition prune and then scan than to index indiscriminately. Of course - there are exceptions. ALTER TABLE MOVE is also something I use a lot. In fact, I do it without a new tablespace name - table will stay in the same and go to the first available "holes". Which eventually frees up the higher extents in a datafile or the whole file. I always use uniform fixed size extents - it indeed makes life a lot easier.

-- 
Cheers
Nuno Souto
in wet Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Feb 02 2013 - 07:03:33 CET

Original text of this message