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

  • From: "TJ Kiernan" <tkiernan@xxxxxxxxxxx>
  • To: <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 1 Feb 2013 11:33:04 -0600

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Rich Jesse
Sent: Friday, February 01, 2013 10:55 AM
To: oracle-l@xxxxxxxxxxxxx
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

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


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


Other related posts: