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

From: Niall Litchfield <>
Date: Fri, 1 Feb 2013 15:25:45 +0000
Message-ID: <>

Do you have any potential downtime windows? Export/Import would be the classic way of doing this.

On Fri, Feb 1, 2013 at 3:15 PM, Rich Jesse <
> wrote:

> Hey all,
> In, we're archiving older data. This first phase will include
> deleting (archiving) about 4 years of data. Ongoing archiving will then
> delete the oldest 6 months of data. Since our DB grows very linearly, I
> expect table sizes to be very stable from then on.
> So I'm trying to recover filesystem space from this first mass archive by
> shrinking the archived segments, then dropping unused datafiles from the
> tablespace, or so I thought. In my testing, empirical evidence suggests
> that Oracle attempts to spread the extents of large segments across many
> datafiles in the TS. One possible important point: the TS was created with
> auto extents. I would have much preferred a static extent size.
> Is there any way short of moving massive numbers segments out of the TS to
> tell Oracle I'd like to drop datafiles? There's about 3400 tables that
> have
> their respective extents intermingled in those datafiles, with ~3100 of
> them
> being a single extent in the first datafile.
> Thoughts?
> TIA!
> Rich
> --

Niall Litchfield
Oracle DBA

Received on Fri Feb 01 2013 - 16:25:45 CET

Original text of this message