Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: switching to locally managed tablespaces

Re: switching to locally managed tablespaces

From: Howard J. Rogers <>
Date: Wed, 3 Jul 2002 07:00:32 +1000
Message-ID: <aft4jj$coe$>

"Glen A Stromquist" <> wrote in message news:ssoU8.55635$
> 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
> > convert, however. It doesn't do an awfully good job: you end up with
> > 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
> > 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.
> > 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
> > right tablespace.
> >
> > Best of luck,
> > HJR
> >
> >
> >
> >
> >
> > "Glen A Stromquist" <> wrote in message
> > news:RxnU8.55176$
> >>
> >> 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
> >> 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
> >> 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
> 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.

HJR Received on Tue Jul 02 2002 - 16:00:32 CDT

Original text of this message