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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 1 Feb 2013 18:02:01 +0000
Message-ID: <CABe10sYm+VuR7Uz3uZ-ffOR_RhVDbDe37wyp5xqQz9yEoHh2RQ_at_mail.gmail.com>



We've run into a horrible bug with dbms_redefinition on 11.2 where a trigger owned by sys for pl/sql scope breaks a unique constraint also owned by sys and terminates the redefining session halfway through with a 604 IIRC we then got the same error inserting into any table with a trigger on it. This may have applied only where the table being redefined had a trigger on it.
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-l


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 01 2013 - 19:02:01 CET

Original text of this message