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: Glen A Stromquist <>
Date: Wed, 03 Jul 2002 15:44:03 GMT
Message-ID: <7ZEU8.19423$>

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" <> 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

> 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" <> 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

> 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
Received on Wed Jul 03 2002 - 10:44:03 CDT

Original text of this message