Re: switching to locally managed tablespaces

In article <aft5au$df2$1_at_lust.ihug.co.nz>, "Howard says...
>
>
>"Pete Sharman" <peter.sharman_at_oracle.com> wrote in message
>news:aft3310d0g_at_drn.newsguy.com...
>> 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, t
>hen
>> >> 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).
>
>True enough, but then export - truncate/drop - import is hardly an online
>operation, is it??!

No, I'd certainly agree with you on that one! :)

>
>>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.
>>
>
>Hadn't thought of that as an option myself, but... yes, it's a good one.
>
>Regards
>HJR
>
>
>
>> HTH. Additions and corrections welcome.
>>
>> Pete
>>
>> SELECT standard_disclaimer, witty_remark FROM company_requirements;
>>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Tue Jul 02 2002 - 18:31:02 CDT