Re: Convert to Locally-Managed Tablespaces

Tanel,

That's a good idea. I briefly considered this, but didn't really dig into it.

The systems I need to do this on is our SAP systems, and downtime is a precious commodity, especially for production.

I just may try this on our test system. The problem with SAP of course, and many other ERP's is that there are 22k+ tables, which could consume a bit of time.

The amount of fragmented space that would be recovered is probably not worth the trouble of this procedure, depending on how much time it takes.

I see that you too need to keep the original tablespace names, is this SAP per chance?

If you have already performed a test of this, what kind of times are you seeing, along with relevant platform information, and the number of tables/indexes?

Jared

On Mon, 2004-01-05 at 16:49, Tanel Poder wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.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).