Search This Blog

Sunday, July 29, 2012

Locally vs Dictionary Managed Tablespaces

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself.

Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and

Tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

DEV> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

------------------------------     ----------------------       --------------------
SYSTEM                           DICTIONARY           USER
SYSAUX                          LOCAL                      SYSTEM
UNDOTBS1                      LOCAL                      SYSTEM
TEMP                               LOCAL                      UNIFORM
ABMD                              LOCAL                      USER
ESSO                                LOCAL                     USER
SIT                                   LOCAL                     USER
USERS                             LOCAL                     UNIFORM
USER_DATA                    LOCAL                     UNIFORM


  1. I am really impressed from this post! The person who created this post is a generous and knows how to keep the readers connected.Thank you. Please keep on posting.


  2. This is really interesting and knowledgeable. Thanks for sharing. I really appreciate it a lot. Please do more blogs in the future. Thank you and God bless to the blogger!