Convert TS from dictionary to locally managed
Evangelos Giannoulas
Has anyone tried to convert dictionary managed ts into locally
using the TABLESPACE_MIGRATE_TO_LOCAL procedure?
Experiences?
Is it safe to do it on a production system?
Thanks,
Vangelis
Steve Ashmore
I did not have any problems. But for the majority of the tablespaces I have
convereted
I have create new LMT's and moved the table/indexes into them them.
This way I knew for sure I was starting off clean. Just me opinion though.
Stephen C. Ashmore
Brainbench MVP for Oracle Administration
http://www.brainbench.com
Author of: 'So You Want to be an Oracle DBA?'
"Evangelos Giannoulas" <g...@pbihag.ch> wrote in message
news:10499700...@rio.ch.pbihag.ch...
Norman Dunbar
I think the usual advice is to create a new LMT tablespace and either
move or import into it directly. There are problems with the conversion
utiltity as it doesn't create uniform sized extents at all but creates
extents with the same size as you have now (if I remember correctly) so
you end up with the same problem on an LMT as you had on the DMT -
un-uniform extent sizes.
Cheers,
Norman.
-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------
FC
off most of the people while doing the operation.
A backup is also recommendable prior to beginning this process.
Bye,
Flavio
"Evangelos Giannoulas" <g...@pbihag.ch> wrote in message
news:10499700...@rio.ch.pbihag.ch...
Howard J. Rogers
>
> 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.
Regards
HJR
Mark D Powell
Vangelis, I believe the package is safe to use; however, because it
can only convert a tablespace to autoallocate and it cannot change the
existing physical allocation of objects in the tablespace plus has a
few unusual and generally undesirable quirks such as still applying
pctincrease after the conversion I would create new tablespaces using
uniform extents like Stephen suggested.
IMHO -- Mark D Powell --
D.Y.
>
> Has anyone tried to convert dictionary managed ts into locally
> using the TABLESPACE_MIGRATE_TO_LOCAL procedure?
>
All that's going to do is move the extent map from the system catalog
to the tablespace itself. Space allocation will still be handled as if
it were a dictionary managed tablespace.
Richard Foote
news:xcfla.11354$1s1.1...@newsfeeds.bigpond.com...
>
> "Evangelos Giannoulas" <g...@pbihag.ch> wrote in message
> news:10499700...@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
Howard J. Rogers
"Richard Foote" <richar...@bigpond.com> wrote in message
news:WFxla.11900$1s1.1...@newsfeeds.bigpond.com...
> "Howard J. Rogers" <howard...@yahoo.com.au> wrote in message
>
> 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 have high availability requirements, then you shouldn't (probably) be
bothering to convert to LMTs in the first place. Presumby, if you're
contemplating monkeying around with Orace-supplied packages and procedures
to achieve the task, you've got some 'unavailable' time up your sleeve.
And since there's no requirement to move every table in one sitting, the
'move and do it properly' approach can be done over an extended period of
time, to fit in with what maintenance windows you have.
I think the more serious obection is the presence of LONGs.
Regards
HJR