What's your opinion on using locally managed vs. dictionary managed tablespaces? Should I create the TABLESPACE with NOLOGGING option & EXTENT MANAGEMENT LOCAL on an OLTP production database? Are there performance benefits? What are the potential disadvantages in the event of a failure?
I noticed after setting up a test database with all my tablespaces as LMT with LOGGING and imported my data using the Oracle import, I ran out of space even though I made the tablespace 24 GB -- two times more than the size of my tablespace.
I never use dictionary-managed tablespaces anymore. I use nothing but LMTs when creating new tablespaces now. There are too many benefits.
First and foremost, you will not have fragmented free space that becomes unusable. LMTs guarantee that even if the free space becomes fragmented, the free space can still be used. For normal database operations, there is no difference in performace between a DMT and a LMT. However, for any operations that allocate or deallocate an extent for a segment, that operation will be faster. Extent allocation/deallocation in an LMT occurs using bitmaps in the data file headers. In DMTs, this allocation/deallocation uses two data dictionary tables to track allocated extents and free extents. And there shouldn't be any problems with failures. In the future, LMTs will be the only available extent allocation method for Oracle tablespaces. DMTs are on the way out the door. I've used them quite successfully without any noticable problems.
I'm not sure why you've run out of space as you've described. Diagnosing that problem would require much more detail.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.