Re: Convert to Locally-Managed Tablespaces

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).