Re: switching to locally managed tablespaces

In article <aft2jm$ahm$1_at_lust.ihug.co.nz>, "Howard says...
>
>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
>
>

Only downside to using the ALTER TABLE MOVE command is it takes out an exclusive lock IIRC i.e. it can't be done online (except for IOT's, that is). AFAIK, the only way to get around that is to use the DBMS_REDEFINITION package in 9i. Unfortunately the OP isn't on that release, but it may be useful for someone else to know this is an option.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Tue Jul 02 2002 - 15:40:01 CDT