Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Convert to Locally-Managed Tablespaces

Re: Convert to Locally-Managed Tablespaces

From: Tanel Poder <>
Date: Mon, 05 Jan 2004 16:49:25 -0800
Message-ID: <>


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

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


Please see the official ORACLE-L FAQ:
Author: Tanel Poder

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 Mon Jan 05 2004 - 18:49:25 CST

Original text of this message