Re: migrating to locally managed tablespaces

On Sun, 19 Jan 2003 21:24:58 +0100, Frederic Payant <> wrote:

>Hi,
>
>Oracle 8.1.7.0 on Solaris 8 (big) box
>I'll have to drive a migration on a huge production database (2
>instances, 700 GB) from dictionary managed TBS to localy managed
>tablespaces.
>
>It's the first time I've to do such a job on a production environment,
>so :
>How can I proceed, are they tools to help, is there an order to follow
>?
>What kind of troubles can I meet ?
>Is there any documentation on this task ?
>
>Thanks for your help
>Regards
>Frederic PAYANT - junior DBA ;-)

Hi Frederic,

There's more than one way to do that task, e.g. :

1. The DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL() proc. converts a DMT to a LMT. Oracle provides full documentation on this. I know that there has, over time, been some dispute over this tool and it's efficiency and reliability. Try checking metalink on this.
2. Create new LMT's on the DB and move the segments in the old DMT's to the new LMT's with :

  "Alter index indx_in_dmt rebuild tablespace NEW_IDX_LMT;"   "Alter table table_in_dmt move tablespace NEW_TABLE_LMT;" etc.

c) Create a new DB with LMT's, export from the old one and import to new one.

Each of these methods has its advantages and disadvantages, regarding downtime, resource requirements etc.

At least with a) and c), a full backup before proceeding is highly recommended.

Good planning and extreme care is required in all 3 cases, but of course, even as a Junior DBA you know that :-) .Good Luck !

• Kenneth Koenraadt