Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: switching to locally managed tablespaces

Re: switching to locally managed tablespaces

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 3 Jul 2002 07:17:14 +1000
Message-ID: <aft5au$df2$1@lust.ihug.co.nz>

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

>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;
>
Received on Tue Jul 02 2002 - 16:17:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US