Re: switching to locally managed tablespaces

Glen A Stromquist wrote:
>
> Yet more questions...
>
> I've been playing with this import by dropping the main data tablespace and
> recreating it with various uniform extent sizes, on my last go around I
> created a 4 gig tablespace and it ended up 95% full with 3.7 gig of data,
> the prod database the export file came from has this same tablespace 3.3
> gig with 2.7 gig of data. When I created the tablespace with extents set to
> auto allocation the data took up about the same space as in the prod db.
>
> How much and what impact does the export file have on importing into a LMTS?
>
> Should I be altering my export params beforehand? ie compress=x?
>
> I am currently re importing the data with the tablespace set to uniform 1
> meg extents, I'll see how that goes. Incidentally the largest table in this
> tablespace has a long raw column had high row chaining in 7.3.3 that I
> could not seem to "fix" by rebuilding the table with higher pctfree etc..,
> in an earlier test migration to 8i with DMTS the row chaining improved
> considerably with the same settings.
>
> TIA
>
> Howard J. Rogers wrote:
>
> >
> > "Glen A Stromquist" <glen_stromquist_at_nospam.yahoo.com> wrote in message
> > news:ssoU8.55635$vo2.3051371_at_news2.telusplanet.net...
> >> Howard J. Rogers wrote:
> >>
> >> > Hi Glen,
> >> >
> >> > Go for it: LMTs are good things, and we like them lots.
> >> >
> >> > Under no circumstances use that procedure Oracle thoughtfully provided
> > to
> >> > convert, however. It doesn't do an awfully good job: you end up with
> > mixed
> >> > extent sizes if you started with them, and half the benefits of LMTs
> >> > therefore just disappear out the window.
> >> >
> >> > If it were me, and if you had sufficient disk space (and given your
> >> > reported size, I think you probably do), I would instead be inclined to
> >> > create a bunch of new LM tablespaces, and simply issue the 'alter table
> > X
> >> > move tablespace Y' command. When everything has been moved, you can
> >> > then just drop the old DM tablespaces, as they'll be empty. You have
> >> > then to rebuild all indexes, which is a slight bummer, but only a
> >> > slight one.
> >> >
> >> > This way you don't have to rely on export/import more than you need to.
> >> > The data stays available in the database. You use simple SQL commands.
> > You
> >> > also get complete control over where to move things (export/import, you
> >> > have to fiddle quite hard because of its propensity to put things into
> >> > tablespaces with exactly the same names as they came from).
> >> >
> >> > If you do the conversion, remember to create a range of extent-sized
> >> > tablespaces (64K, 512K, 1M, 8M, 64M), and move the right table(s) into
> > the
> >> > right tablespace.
> >> >
> >> > Best of luck,
> >> > HJR
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > "Glen A Stromquist" <glen_stromquist_at_nospam.yahoo.com> wrote in message
> >> > news:RxnU8.55176$vo2.3045819_at_news2.telusplanet.net...
> >> >>
> >> >> On the database that I am upgrading from 7.3.3 to 8.1.7 I just created
> >> >> DMTS's on the 8.1.7 instance then did my import. all went ok, but
> > since
> >> >> there is no mad panic to get this into production I was thinking on
> >> > blowing
> >> >> the tablespaces away, re creating the tablespaces as locally managed,
> >> >> then redoing the import.
> >> >>
> >> >> Any particular gotcha's I should look out for here?
> >> >>
> >> >> This DB is less than 4 gig, so I am not talking a huge database here.
> >> >>
> >> >> Is this the best method?, or should I use the Oracle supplied package
> > to
> >> >> convert the tablespaces from DMTS to LMTS instead?
> >> >>
> >> >>
> >> >> TIA
> >>
> >> Thanks Howard -
> >>
> >> I opted to "blow away" the old tablespaces and are in the process of
> >> recreating them as I am writing this, since I have to refresh the DB with
> > a
> >> fresh export/import from the current prod db before I put it this one
> >> into production it is not a big deal to have the data available at all
> >> times. The only thing I am waffling on is whether to give them uniform
> >> extents & what size if so, or leave it to auto allocate.
> >
> >
> > The autoallocate policy is OK, but somewhat weird. Think of it as
> > PCTINCREASE on steroids. There are problems with autoallocate in
> > particular circumstances: temporary tablespaces (from memory) can't use
> > them (and from knowledge it would be a poor choice even if they could). I
> > think rollback segments are also a poor choice for autoallocate, since you
> > want all extents to be of the same size, and autoallocate practically
> > guarantees they won't be.
> >
> > For everything else, they'd be OK, except that you may discover that a
> > single insert suddenly causes the acquisition of a mammoth extent, because
> > you've just tripped over into the next extent allocation size.
> >
> > I'd still recommend uniform extent sizes, in a range of sizes, as I
> > indicated earlier; when extent numbers get too high, you simply move the
> > table into the 'next size up' tablespace. You get predictability, and
> > consistency, and still have relatively easy management.
> >
> > Regards
> > HJR
If an object occupies 'n' bytes, then into a (uniform) LMT you will allocate the next highest multiple of the uniform size. For example, if your uniform size is 200m, then a 311m table will use 2x200m

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."