The Blog from the DBA Classroom

By: Joel Goodman

How Good is your “Local” Management?

Posted by Joel Goodman on 23/11/2009

Whilst delivering a course recently, a delegate asked for my views about tablespace management and the ensuing discussion covered Locally managed tablespaces, bigfile tablespaces, and Automatic Segment Space Management (ASSM)  for tablespaces. During the discussion I surprised the class when I stated “there are actually three types of locally managed tablespaces” as only two were known to anyone in attendance.  It then occurred to me that this was one of those cases where things may have “fallen into the cracks” and that it should be clarified.

Locally managed tablespaces may be created by a DBA with one of the following two options:

  • Uniform Size
  • Autoallocate

When using the “Uniform Size” option, all free and allocated extents for the tablespace are of the specified size and each bit in the bitmap used to track storage represents an area of that size. This option provides the following advantages:

  1. Extent tracking is local, being done in a bitmap metadata area within one of the tablespace files. This is the primary and well known advantage for local management and eliminates the use of the Data Dictionary for tracking free and allocated extents in base tables UET$ and FET$. In doing so there is also a reduction in contention for the Space Transaction enqueue (ST enqueue) which is used to single thread updates to space management tables in the data dictionary.
  2. External Fragmentation in the tablespace is totally eliminated since all extents are the same size. Therefore, the first free extent found by reference to the bitmaps is chosen for allocation when space is required for a segment creation or extension.

There are also some important implications for the “Uniform Size” option.

  1. Large uniform sizes may waste storage is small segments are created in the tablespace.
  2. The DBA may require several different tablespaces, each using a different “Uniform Size” if there is a great variety in segment sizes.

When using the “Autoallocate” option, each bit in the bitmap used to track storage represents an area of 64K. This option provides the following advantages:

  1. Extent tracking is local as in the “Uniform Size” option providing the same benefit in reducing ST enqueue overhead.
  2. Extents may vary in size on multiples of 64K boundaries, providing for heterogeneous segment sizes in the same tablespace with little or no wasted storage.
  3. Although all extents are multiples of 64K, when an extent is dropped the storage it occupies may be reallocated as several separate extents if required. External fragmentation, although possible, may not be as common as for dictionary managed tablespaces where extents are allocated on five block boundaries.

The third option which is not well known is the “Converted” tablespace. DBAs who wish to convert dictionary managed tablespaces to locally managed ones, use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.

Argument Name                  Type                    In/Out Default?

TABLESPACE_NAME                VARCHAR2                IN
UNIT_SIZE                      BINARY_INTEGER          IN     DEFAULT
RFNO                           BINARY_INTEGER          IN     DEFAULT

The Parameters passed to this procedure are :

  1. The name of the tablespace to convert which is a mandatory parameter.
  2. The unit size that will be represented by each bit in the bitmap. This defaults to the highest common factor for all the existing allocated and free extents in the tablespace at the time the conversion is done.
  3. The tablespace relative file number in which the bitmap should be created. This defaults to the lowest file number.

When using a “converted” tablespace the only real benefit accrued is the local management as in the two cases listed above. But unlike “Uniform Size” and “Autoallocate”, the fragmentation propensity for a converted tablespace remains as high as it is for Dictionary managed tablespaces. This is caused by the default unit_size calculation being set to the highest common factor of existing extents. Since the extent boundaries in dictionary manages tablespaces are on five block boundaries, it is usually the case that some or many extents in such a tablespace are only five blocks long. The highest common factor therefore is often a size of five blocks. If the tablespace was in a database with 4k blocksize, then the highest common factor will often be 20k unless the DBA had arranged to have larger minimum extent lengths before the conversion using the minimum extent length option available since Oracle 8.0.

It is for this reason that I do not recommend converting tablespaces but instead, prefer to create new ones and then move the contents over. Do do so I recommend the following:

  1. Index Tablespaces – Create a new tablespace and then do “online index rebuilds” into the new tablespace and drop the old one when finished.
  2. “Table” Tablespaces – Create a new tablespace and then use “online table redefinition” with the DBMS_REDEFINITION package, solely for changing the tablespace without changing the table’s logical or physical properties.

Finally, to determine if your database has any “converted” locally managed tablespace consult the DBA_TABLESPACES data dictionary view:

SQL> select tablespace_name,extent_management, allocation_type from dba_tablespaces;


SYSTEM                         LOCAL      SYSTEM
SYSAUX                         LOCAL      SYSTEM
UNDOTBS1                       LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM
USERS                          LOCAL      SYSTEM
UNDOTBS2                       LOCAL      SYSTEM
EXAMPLE                        LOCAL      USER
FRA                            LOCAL      SYSTEM

If the EXTENT_MANAGEMENT is LOCAL and the ALLOCATION_TYPE is USER then the tablespace has been converted. Creating new ones and moving segments into them can reduce fragmentation in those tablespaces and provide you with proper “Local” management.


5 Responses to “How Good is your “Local” Management?”

  1. […] 8-How does locally managed tablespaces work and third type of locally managed tablespaces? Joel Goodman-How Good is your “Local” Management? […]

  2. John, I had a demo database years ago 8i or 9i time frame but alas i have no more databases with dictionary managed tablespaces. But the fragmentation of converted tablespaces is the same as if it were dictionary managed and that was a well known problem for large tablespaces in which objects of different sizes were created and dropped over a period of time. The degree of fragmentation in a dictionary managed tablespace and the propensity for more will reamin after converting which is wy i dont recommend this method except for the system tablespace.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: