Re: Convert TS from dictionary to locally managed

"Howard J. Rogers" <howardjr20002_at_yahoo.com.au> wrote in message news:xcfla.11354$1s1.177102_at_newsfeeds.bigpond.com...
>
> "Evangelos Giannoulas" <gie_at_pbihag.ch> wrote in message
> news:1049970085.257241_at_rio.ch.pbihag.ch...
> > Hi,
> >
> > Has anyone tried to convert dictionary managed ts into locally
> > using the TABLESPACE_MIGRATE_TO_LOCAL procedure?
> >
>
> I wouldn't touch it with a barge-pole. It does a useless job, resulting in
a
> strange hybrid of a beast: a locally managed tablespace with odd-sized
> extents which can fragment just like a dictionary-managed one.
>
> Since one of the main advantages of LMTs is that they don't fragment,
> because they use uniform extent sizes, this is a serious failing.
>
> It's all the more serious because there's no need ever to convert in the
> first place: just create a new LMT, and use the 'alter table X move' and
> 'alter index Y rebuild' commands to move your segments into the new
> tablespace. Voila: LMT with 'proper' fixed extent sizes.
>

Hi Howard,

You make it seem all so easy and straight forward.

A bit of a problem though.

If you have large quantities of data to "move" and you have high availability requirements, then it's not so easy as the table locks become preventative.

If you simply can't afford the unavailability of data during the conversion process, although not ideal, the above procedure is a possible "half-way" solution to convert to LMT and reduce DD issues . If objects are only growing and are not likely to be dropped, it has even more appeal. If such availability restrictions apply, the dbms_redefinition procedure is another workable, albeit messy, solution.

Hopefully 10i will provide for the "easy" online movement of heap tables ....

Cheers

Richard Received on Fri Apr 11 2003 - 06:42:26 CDT