Re: switching to locally managed tablespaces

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
Received on Tue Jul 02 2002 - 15:30:40 CDT