Locally Managed Tablespaces vs. Dictionary managed

[saneja]

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

[stecal]

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.

[slimdave]

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.

[stecal]

Oops, I misread the "without exp/imp" part of the question.

[marist89]

Originally posted by stecal
Oops, I misread the "without exp/imp" part of the question.

How about MOVE/REBUILD...

[davey23uk]

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

[slimdave]

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.

[davey23uk]

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

[saneja]

thanks to all who posted their comments.

[jlakhani]

Can someone give a concrete example of when you might want to migrate your dictionary managed tablespabes to LMT?