Re: switching to locally managed tablespaces

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

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