Locally Managed Tablespaces vs. Dictionary managed
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Locally Managed Tablespaces vs. Dictionary managed

  1. #1
    Join Date
    Nov 2001
    Posts
    14

    Locally Managed Tablespaces vs. Dictionary managed

    How hard is it to change a production dictionary managed tablespace into a locally managed tablespace ..Can this be done without exp/imp?

    Are there any performance benefits over DMT..
    What are the convincing reasons to put LMT instead of DMT for existing production tablespaces.

    Is it really worth it?

    Please give your ideas. I am looking for practical answers who have really implemented LMT's and gained some benefits

    thanks in advance

  2. #2
    Join Date
    May 2002
    Posts
    2,645

    Re: Locally Managed Tablespaces vs. Dictionary managed

    Originally posted by saneja
    How hard is it to change a production dictionary managed tablespace into a locally managed tablespace ..Can this be done without exp/imp?
    Not hard at all. Create a new database with the tablespaces as LMT's, then do the import of the data.


    Are there any performance benefits over DMT..
    Enough that DMT's will be going by the wayside. In future releases of Oracle, all tablespaces will be LMT's.


    What are the convincing reasons to put LMT instead of DMT for existing production tablespaces.
    See Note 105120.1 on MetaLink
    A Locally Managed Tablespace is a tablespace that manages its own extents maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for tablespace quota information), unlike the default method of Dictionary - Managed Tablespaces. ** Reduced recursive space management ** Reduced contention on data dictionary tables ** No rollback generated ** No coalescing required


    Is it really worth it?
    Enough that Oracle Corp. is making all TS's LMT's in the future.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Locally Managed Tablespaces vs. Dictionary managed

    Originally posted by saneja
    How hard is it to change a production dictionary managed tablespace into a locally managed tablespace ..Can this be done without exp/imp?
    In resent Oracle versions you can use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL() procedure to do this, or you could create a new set of tablespaces on your existing database and MOVE the tables/indexes. You don't necessarily need to create a new db.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Oops, I misread the "without exp/imp" part of the question.

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by stecal
    Oops, I misread the "without exp/imp" part of the question.
    How about MOVE/REBUILD...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    In resent Oracle versions you can use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL() procedure to do this
    not always the best option as you lose one of the main benefits of LMT's - uniform extents

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    That's true, but there's no reason not to have uniform extents in your DMT already -- it's just not something that's forced on you.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    even if you set inital = next then you still dont truely have uniform extents

    check this out for an explantion from asktom

    Well, if we have initial=next and pctincrease=0 in DMT and use dbms_space_admin to migrate to LMT, then we can get uniform extents that is get the full benefit
    of LMT. What are your thoughts


    Followup

    Not really, a migrated tablespace won't have the bitmaps at the "front" like a natural one would -- and the extents probably aren't all exactly the same, the algorithm allows for some rounding and different sized extents in a DMT even with intial=next and pctincrease=0

  9. #9
    Join Date
    Nov 2001
    Posts
    14
    thanks to all who posted their comments.

  10. #10
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Can someone give a concrete example of when you might want to migrate your dictionary managed tablespabes to LMT?
    "High Salaries = Happiness = Project Success."

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