drak0nian_at_yahoo.com (Paul Drake) wrote in message news:<1ac7c7b3.0310022156.5b252b95_at_posting.google.com>...
> "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
Thanks for the responses Gentlemen
Vince
Received on Fri Oct 03 2003 - 08:58:07 CDT
