Has anybody been using the locally managed tablespaces feature of 8i? I am trying to gather some information on this feature but I have only been able to find the advantages of this feature. Can any of you guys tell me what might be the downside to this feature? I want to know if this feature is worth trying in a production environment. Yes, I will try it in a pre-production environment before I put it in the production environment.
I found it beeing nice: fast and defragmented.
And no problems with exporting/importing test(development) schemas to production database and vice versa: storage parameters like initial extents are ignored.
I hope by now most of us know what is the difference between the Locally managed table space and the data dictionary.
In case here is the differences:
Dictionary-Managed: when ever a tablespace is set to dictionary managed, it ends up using the data dictionary to manage its extents and forces the oracle server to update the appropriate tables in the data dictionary when ever an extent is allocated or deallocated.
Locally Managed: These tablespaces manages their own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. As a result it sets each bit in the bitmap to block or group of blocks and as a result it just modifies the bits when ever an extent is allocated or freed.
Now Considering the advantages of Locally managed tablespaces:
It avoids recursive space management operations.
Since they do not record the free space in the data dictionary, it avoids/reduces the contentions on these tables.
Since they keep track of their ajacent free space, they avoid coalece of free extents
All extents can have the same extent size
Finally the changes to the extent bitmap do not generate rollback information, since they do not update tables in the data dictionary.
As a result of the above said advantages they promiss a better performance. One thing it is worth the notice is that
"Locally managed tablespace cannot be used for the system" and for every thing else it can be used to reap the performance.
So far, as every one had pointed out we haven't seen any down side of using this, unless and other wise oracle introduces some kind of bug in the future relseases