Convert Dictionary-managed tablespace to local-managed tablespace

[kew888]

Hi, Folks:

I have one question about the tablespace convertation.

I have one schema named A. There are 100 tables
created based on Tablespace B and 200 indexes on
Tablespace C.

To convert the Tablespace B & C from dictionary-manage
to Local-manage. I can drop all indexes and re-create the
Tablespace C as local-manage tablespace. That's workable.

However, I don't know how to convert the Tablespace B
to loca-manage tablespace. What I thought first is to export
all data, drop the B including contents, re-create B as local-
manage tablespace. Import data back.

But when I import the data, the Table structure is not
match. It failed.

Now, I am thinking about re-create those 100 tables
again on the new local-manage tablespace B.

But I don't know is there any script can allow me to
re-create all the tables under Schema A.

Is there anyone can help me with this ? Thanks.


Ke

[mzahid]

Please be more specific ...
What is your database version?

[pando]

dont export the tables, move them to another tablespace while you recreate your original tablespace to local, once created move them back

alter table xxxx move tablespace etcetc

rebuild the indexes at the end

[mzahid]

This script is only available if you are using Oracle 816 or higher version of database and this is achieved via the dbms_space_admin api.

dbms_space_admin.tablespace_migrate_to_local()

But there are restrictions :

Tablespace cannot be system , offline, bitmapped or Temporary and there must be enough space in the tablespace to accomodate the bitmaps.

If there are any problems with the Locally managed scheme, users can still abandon this and return to dictionary management using:

dbms_space_admin.tablespace_migrate_from_local()

Visit : http://otn.oracle.com and download Oracle8i Adminsitration Guide for more details on locally managed tablespace.

Hope this helps!!!!

[pando]

before you use dbms_space_admin you better check Note 120061.1 from Metalink why I wouldnt use it

You cannot use LMT´s uniform size or auto allocate feature if you migrate from dict to local (well if I want to use lmt one main reason is I want to use uniform size)

[TimHall]

The main reason I use locally managed tablespaces is to reduce contention on the dictionary tables. This is still true of a converted locally managed tablespace.

If you can reorganize your tablespaces do so, if not just convert them and schedule in a reorganization at a later date.