Convert Dictionary-managed tablespace to local-managed tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Convert Dictionary-managed tablespace to local-managed tablespace

  1. #1
    Join Date
    Mar 2002
    Posts
    1

    Post

    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

  2. #2
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87

    Convert Dictionary-managed tablespace to local-managed tablespace

    Please be more specific ...
    What is your database version?
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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

  4. #4
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87

    Convert Dictionary-managed tablespace to local-managed tablespace < Last Thread

    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!!!!
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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)

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width