Re: Convert to Locally-Managed Tablespaces

A small addition, PK/UQ constraints have to be disabled in order to drop their indexes (and index definitions should be exported before disabling constraints, because implicitly created indexes will be automatically dropped if a constraint is disabled (without keep indexes option)).

Tanel.

> 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

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