Re: How to coalesce an LMT TS to drop extra datafiles?
- From: Nuno Souto <dbvision@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 02 Feb 2013 17:03:33 +1100
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@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
