RE: How to coalesce an LMT TS to drop extra datafiles?
Date: Fri, 1 Feb 2013 11:33:04 -0600
Message-ID: <3DC440E2338A24439D527C2E64E1ECBCA85386_at_deeds.pti-nps.com>
DBMS_REDEFINITION will handle *most* of the rename/cleanup for you, if you let it. Look at the copy_dependents & register_dependents procedures.
The only place I can remember it falling down is on stored code (packages/views/etc) - those need to be recompiled after you finish the redefinition (or you can wait until they error once and the automatic recompile should handle it for you). Synonyms may also get invalidated, but I use those so sparingly that my memory is mostly cloudy on that one.
HTH,
T. J.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Friday, February 01, 2013 10:55 AM
To: oracle-l_at_freelists.org
Subject: Re: How to coalesce an LMT TS to drop extra datafiles?
Wolfgang posts:
> I forgot ( to mention ) that "rebuild index(es)" part of the alter
> table move operation.
I've resigned myself to the fact that I'm going to have to move the tables once shrunk. But as MOVE operations require exclusive locks during the operation, I was considering DBMS_REDEFINITION. (DB is EE if there's restrictions) I like the idea of online moving all segments to the new TS then taking a short outage to rename/cleanup/etc.
Anyone used that package before? Any gotchas or bugs?
Thanks all for the input so far!
Rich
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 01 2013 - 18:33:04 CET