Re: Migrating to from dictionary to Locally managed tablespaces

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3f7c89a4$0$12033$afc38c87_at_news.optusnet.com.au>...
> Vincento Harris wrote:
>
> > I am trying to migrate from dictionary managed to locally managed
> > tablespaces.
> > to be able to use uniform extents using IMP/EXP .
> > 1)Any drawbacks.....
>
>
> To using locally managed tablespace? None.
> To migrating to locally managed tablespace? Possibly. Depends on how you
> propose to do the 'migrating'.
>
> >
> > My understanding is that
> >
> > 1)The initial and next extents which I am trying to change will not be
> > changed.
> > as the imp will define initial and next as they existed in last
> > table.
>
> Import will be saying 'create table BLAH with initial 100K next 567K'. The
> tablespace, which uses (shall we say) uniform 64K extents will turn around
> to the create table statement and say, 'you can't have 100K. But I can give
> you two 64K extents, which is close enough'.
>
> Initial is not ignored by locally managed tablespaces, therefore. It is
> honoured in its intention, at least. You ask for 623K, you'll get 10 64K
> extents.
>
> Next, however, is completely ignored. Ask for 567K for the table's next
> extent, and it is given 64K (or, of course, whatever extent size you
> decided on for your tablespace).
>
> >
> > Is there a better way to do this, I have a couple of thousand tables
> > and about 70 tablespaces supplied with APP. Oracle 8.1.7.4 HP Unix
> > 9000
> >
>
> Is there a better way to do what???
>
> Regards
> HJR
>
> > Vince

Vince,

do you mean, move the data into a 9.2.x database, perhaps? that could be "better" in some respects, possibly worse in others.

Personally, I'd recommend testing the app code against a 9.2 db prior to the migration. Found more than a few interesting behaviors from the CBO, usually due to sloppy code that slid by in 8.1.7.x, had poor execution plans in 9.2 - 3 orders of magnitude more cpu consumption in a few cases. It was a good stress test for the server, a bad stress test for the users (and support staff).

If you're not going to migrate, create the new tablespace as locally managed, alter table <table_name> move and rebuild the indexes after in a new locally managed tablespace.

whether you rebuild the indexes in their own tablespace I won't get into. ;)
(re)build them nologging, in parallel if you have enterprise edition.

Paul Received on Fri Oct 03 2003 - 00:56:09 CDT