How to coalesce an LMT TS to drop extra datafiles?

From: Rich Jesse <rjoralist2_at_society.servebeer.com>
Date: Fri, 1 Feb 2013 09:15:05 -0600 (CST)
Message-ID: <d67996b695dfc8315e25cc649c1a8c01.squirrel_at_society.servebeer.com>



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
Received on Fri Feb 01 2013 - 16:15:05 CET

Original text of this message