Converting between DMT and LMT

I should keep in mind while converting.

Oracle does include procedure in a supplied package to help you convert a DMT to a LMT. The DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL will convert a DMT to a LMT. Similarly, the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL will convert a tablespace from a LMT to a DMT.

That being said, I have never been comfortable with converting a DMT to a LMT. Personally, I would create a new tablespace to replace the existing DMT. I would then move the tables to the new LMT with the ALTER TABLE MOVE command. I would move indexes with the ALTER INDEX REBUILD command. Both of these operations can be performed with the ONLINE option so as to not shut out your application users while the segment is being moved.

This approach has a few benefits. First, you can take this opportunity to recreate the tablespace with new datafiles or perhaps in a new location. Second, you can take this opportunity to move different segments to different tablespaces as part of a database reorg if necessary. The supplied procedure will not let you perform any of these options at the same time.

Once you have emptied your DMT, you can drop it. If you were partial to the old tablespace name, Oracle 10g lets you rename the tablespace back to the one you like.