I'd like to know your opinion about what kind of tablespace to choose for. Oracle documentation says about the advantages of locally managed tablespaces, like "simplified space allocation", "no need to coalesce" etc.
Have you used locally managed tablespaces before? What I want to know is: Locally managed tablespaces are ALWAYS the best choose? If not, in what situation is better to use dictionary managed tablespaces?
Please give me some "real life" feedback about it.
I had read an article in one of the recent Oracle magazine, which discussed the use of locally managed ts. It said that it's use is ideal for Temp ts. I suggest you take a look at it.
A quote from Mr. Thomas Kyte from Oracle Corp.:
"I no longer use dictionary managed tablespaces for anything other then SYSTEM (which must currently be dictionary managed)."
If you don't know who Tom Kyte is - he is one of the most knowledged persons in this planet regarding Oracle RDBMS. He has been helping Oracle users community termendously for last few years over usenet discussion groups comp.databases.oracle.*. Now he has his column in Oracle Magazine ("Ask Tom") and also publish his answers to various questions about Oracle daily on http://asktom.oracle.com.
I have been using locally managed tablespace for a few months and I havent faced any problems yet. It has indeed many advantages, by using locally managed tbs I dont need to worry about sizing objects, fragmentation etc
However I havent faced a recovery situation yet, I guess it should be same as those dictionary managed tablespaces but I will have to start testing it soon before I come across a recovery situation with locally managed tablespaces :o