| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Convert to Locally-Managed Tablespaces
Hi,
I just use vi :%s/oldtablespacename/newtablespacename/g to edit the dump file and import it back.In my case, it did work. And it is oracle supported method of renaming tablespace in versions before 10g.
Manual parallel means:
I have to move 10 table, for example, so in one script, I will do:
alter table a move parallel(10) nologging;
alter table b move parallel(10) nologging;
etc.
With manual parallel, assume, table a-d has 100m, table e-f has 200m, table g has 400m, I will do:
script a: alter table a move nologging; alter table b move nologging; alter table c move nologging ...
script b: alter table e move nologing; alter table f move nilogging;
script c: alter table g move nologging;
And I run the three script in the same time.
Regards.
Zhu Chao.
> Two questions for you:
>
> 1. The export file is binary format. Can you tell me
> which editor you use?
>
> 2. How to manually parallel?
>
> Thanks.
>
>
> --- zhu chao <chao_ping_at_vip.163.com> wrote:
> > Hi, Tanel:
> > If you have plenty of downtime, everything will
> > be ok.
> > If you want to further limit the downtime, here
> > is some suggestions:
> > 1. I think manual parallel will always be better
> > than oracle parallel. So I always use script, that
> > means, I split the move table nologging script to 10
> > scripts and let them run concurrently. I call it
> > manual parallel:).So does the index rebuild.
> >
> > 2. To avoid that huge table move again, we can
> > consider using transportable_tablespace feature. I
> > did test on my linux 920 box, but not in production.
> > It did work. Steps like:
> > set tablespace read only;
> > check self constrainted.
> > exp the metadata.
> > edit the metadata dump, replace tablespace with
> > new_tablespacename;
> > drop the tablespace.
> > imp back the metadata back.
> > (The above is from oracle metalink ,but I forget
> > about the noteid).
> >
> > regards
> > Zhu Chao.
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L"
> > <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, January 06, 2004 8:49 AM
> >
> >
> > > Hi!
> > >
> > > > This is what I will need to use on our systems,
> > as there are about 400 gig
> > > > of data and indexes. 200 gig of data is too
> > large to export/import, at
> > > least
> > > > it is for this project. So dbms_space_admin it
> > will be.
> > >
> > > I'm about to do a reorg+conversion of a 250GB
> > 8.1.6 database in next week,
> > > here's what I'll do (there is practically no free
> > space for temporary
> > > usage):
> > >
> > > 1) Export index definitions (normal export with
> > rows=n)
> > > 2) Drop all indexes
> > > 3) use alter table move with parallel 16 and
> > nologging to move all tables to
> > > old index tablespaces (the indexes consumed more
> > space than tables)
> > > 4) drop and recreate data tablespaces
> > > 5) use alter table move again to move tables back
> > (the segments have to
> > > reside in original tablespaces, otherwise I could
> > have skipped this step)
> > > 6) drop and recreate index tablespaces
> > > 7) get index definitions out of exportfile and
> > modify them to add parallel &
> > > nologging (with big sort area size)
> > > 8) rebuild indexes
> > > 9) do a full backup
> > >
> > > It might help to recreate index tablespaces even
> > before step 3, to speed up
> > > parallel table moving a bit..
> > >
> > > Maybe you want to test this Jared, this approach
> > is much faster than
> > > export/import, because everything can be done with
> > direct path operations
> > > and nologging (import doesn't have direct path
> > facility, so regular array
> > > inserts are used, which always require logging as
> > well).
> > > Also, your tables/datablocks will be optimized
> > after moving them (which is
> > > not the case with dbms_space_admin) and you don't
> > have to have any space for
> > > reorg in case your cleared index tablespace can
> > temporarily accommodate your
> > > data.
> > >
> > > > IIRC one of the drawbacks of using
> > dbms_space_admin to convert is
> > > > that you won't be converting to nice uniform
> > extent sizes for existing
> > > data.
> > >
> > > Yes, and if your tablespace is fragmented, the
> > fragmentation will remain
> > > there, despite your conversions (of course,
> > smaller extents might be able to
> > > use some of this fragmented space later on).
> > >
> > > Tanel.
> > >
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > > --
> > > Author: Tanel Poder
> > > INET: tanel.poder.003_at_mail.ee
> > >
> > > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > > San Diego, California -- Mailing list and
> > web hosting services
> > >
> >
> ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > > (or the name of mailing list you want to be
> > removed from). You may
> > > also send the HELP command for other information
> > (like subscribing).
> > >
> > >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.net
> > --
> > Author: zhu chao
> > INET: chao_ping_at_vip.163.com
> >
> > Fat City Network Services -- 858-538-5051
> > http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> > hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be removed
> > from). You may
> > also send the HELP command for other information
> > (like subscribing).
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
> http://hotjobs.sweepstakes.yahoo.com/signingbonus
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: dba1 mcc
> INET: mccdba1_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: zhu chao INET: chao_ping_at_vip.163.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Jan 06 2004 - 19:39:34 CST
 |
 |