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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 1 Feb 2013 15:25:45 +0000
Message-ID: <CABe10sbT5E2uryMBjR1rM_0H4_8rdMfV9tj792UsG2SHqHK_MQ_at_mail.gmail.com>

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 <rjoralist2_at_society.servebeer.com
> wrote:

> Hey all,
>
> In 11.2.0.3.0, 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

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