Register Login

Difference Between Dictionary Managed and Locally Managed Table space.

Updated Jun 21, 2018

Dictionary Managed Tablespace vs Locally Managed Tablespace

Hello Experts,

What is the difference between Dictionary Managed and Locally Managed Tablespace?

Please assist me.

Thanks in advance.


Comments

  • 27 Mar 2015 5:31 pm Abhijeet Mudgal Helpful Answer

    Locally managed table space came in picture to get rid of space transaction whenever there was need of space for next allocation of extent and independent of sys schema.

    Let me explain in simple way:

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

    Table spaces that record extent in dictionary are called dictionary table spaces managed by sys schema as free space in sys.FET$ and used space in sys.UET$.

    And whenever there is need of space transaction it has to depend on these.

    Table spaces that record extents in the header of the data file these are called locally managed table space.whenever space is required they do not depend on any for used and free space as the table space itself manages it performance increases dictionary contention is reduced.

  • 21 Jun 2018 9:56 am Sugandh Helpful Answer

    There are two types of tablespace

    1. Locally managed tablespace 
    2. Dictionary managed tablespace 

    1st locally managed tablespace > information of your extent (free or used, size etc..) is stored in the header of datafile ok 

    2nd dictionary managed tablespace > means the information of ur extent is stored is data dictionary (system tablespace ) which effect in performance so oracle recommend locally managed tablespace if ur system tablespace locally managed tablespace then you cannot create dictionary managed tablespace in your database ok but if your system tablespace is dictionary managed then you can make both ok any doubt then ask me.


×