Benefits of Locally Managed Tablespaces

Locally managed tablespaces are an Oracle feature that was introduced in Oracle version 8i. There are several benefits associated with locally managed tablespaces, as well as a few restrictions. In this topic we will cover locally managed tablespaces in detail.

What are Locally Managed Tablespaces?

Tablespaces can be created using one of two types of space management. The traditional tablespace is created as a dictionary-managed tablespace. When dictionary managed tablespaces are used, the allocation and deallocation of space for the tablesapce is managed via the data dictionary in the SYSTEM tablespace. One data dictionary table is SYS.UET$ to track used extents. The other data dictionary table is SYS.FET$ to track free extents.

There are negative implications to the use of dictionary-managed tablespaces. Since they use the data dictionary tables, which are contained in SYSTEM tablespace, there is a redo and UNDO generation associated with space allocations and deallocations within the tablespace. Contention for the two data dictionary tables can also lead to poor performance. Other performance problems can be caused by the taking of shared pool latches as these dictionary tables are updated.

Locally managed tablespaces were introduced in Oracle8i to eliminate the space management performance overhead associated with dictionary-managed tablespaces, and to provide some additional functionality not available with dictionary tablespaces. Instead of managing space in the SYSTEM tablespace, locally managed tablespaces manage all space allocations and deallocations within the tablespace datafiles themselves, using bitmaps. The bits in the maps determine if a section of space is used (turned on) or free (turned off). Locally managed tablespace bitmaps eliminate the need for recursive SQL to be executed against the data dictionary, and reduces the redo and UNDO components associated with accessing the data dictionary. As previously noted, locally managed tablespaces were first introduced in Oracle8i, and have become the default space management method used in Oracle9i and Oracle 10g.

Locally Managed Tablespaces and Transportable Tablespaces

Beginning with Oracle 8i tablespaces can be transported between different databases. This has significant benefits when moving large amounts of data between different databases. For example, if you have a large amount of OLTP data that you wish to move into your data warehouse, you can store that data in a transportable tablespace. You can then transport a copy of that tablespace to another database, and plug it in. Tablespaces do not have to be locally managed to be transported to another database.

Oracle9i provides even more benefits when using locally managed tablespaces. This is because Oracle9i allows you to create a locally managed tablespace with a block size that is different than the default block size of the database. This allows you to have multiple tablespaces of varying block sizes in your database. Further, this feature allows you to transport tablespaces across databases of varying block sizes. This removes a restriction in Oracle8i that requires that transported tablespaces be of the same block size.

Oracle 10g removed one more restriction to transportable tablespaces. Starting with Oracle 10g, the source and destination platforms do not need to be the same. Prior to Oracle 10g, you could only transport a tablespace from one platform to the same platform, i.e. Windows to Windows, or Solaris to Solaris, or Linux to Linux. Oracle 10g lets you transport tablespaces across platforms, for example from Windows to Solaris, or from Solaris to Linux. In order to transport tablespaces from one platform to a different platform, both the source and the destination databases must have COMPATIBLE set to at least 10.0.0.

Other Benefits of Locally Managed Tablespaces

Aside from the performance issues previously mentioned, there are a few other advantages of locally managed tablespaces. One of the benefits that eases administration is how the Oracle database allocates space in a locally managed tablespace. The DBA has the option of letting the database automatically determine the extent size or have all extents in the tablespace have one uniform size. The auto-allocation method of extents frees the DBA from having to worry about extent sizes. The uniform method of extents ensures that each and every extent in the tablespace is the same size. Dictionary managed tablespaces do not enforce uniform extent sizes in a tablespace.

Another benefit of locally managed tablespaces is that free space does not have to be coalesced. In dictionary managed tablespaces, it is the SMON process that occasionally wakes up and coalesces adjacent chunks of free space. This overhead operation can cause some performance issues. Locally managed tablespace chunks of free space are either one uniform size, or in the case of the autoallocate method, integer multiples of the smallest chunk of free space. Any free space will always be large enough to be used; therefore it does not have to be coalesced into larger chunks of free space. This brings one other benefit to locally managed tablespaces; the tablespace will not become fragmented so as to make small, unusable chunks of free space. The DBA will not have to perform any reorganization task on these tablespaces due to free space fragmentation.

Another benefit of locally managed tablespaces is that standby databases can be opened in READ ONLY mode. This is made possible because locally managed temporary tablespaces do not generate any undo or redo. If the local temporary tablespace is dictionary managed, then undo and redo is generated and the standby database cannot be opened for reporting.

Oracle 10g introduced the concept of a bigfile tablespace. Bigfile tablespaces are helpful in very large databases (VLDB). Instead of a tablespace consisting of multiple small datafiles, the bigfile tablespace is created with a very large (up to 4G blocks) datafile. The bigfile tablespace can only be locally managed with auto segment space management. If the block size of the tablespace is 2K, then the bigfile tablespace can be as large as 8TB. If the block size of the tablespace is 32K, then the bigfile tablespace can be as large as 128TB. This can significantly increase the maximum size of the overall Oracle database.

Creating Locally Managed Tablespaces

To create a locally managed tablespace on an existing database use the local option of the extent management clause of the create tablespace command. Here is an example of the creation of a locally managed tablespace:

DATAFILE ‘/u01/oracle/data/DB1/large_tables_01.dbf’

When a locally managed tablespace is created, you can configure two different extent allocation methods. These two methods are autoallocate or uniform. In the previous example we demonstrated the use of the autoallocate option. When the autoallocate method is selected, Oracle will select the initial and next extent sizes for all objects in the tablespace. Autoallocate is the default space management method for a locally managed tablespace. Many of today's DBAs are letting Oracle manage the extent sizes in their tablespaces. No longer does the DBA need to worry about determining appropriate values for the initial and next extents for their database segments. Originally, the best practices of the Oracle DBA said to create locally managed tablespaces with uniform extent sizes. As the Oracle community has researched, tested, and grown accustomed to autoallocate, the best practices of today's Oracle DBA is to use the autoallocate option for most locally managed tablespaces.

When you specify autoallocate, Oracle will allocate extent sizes of 64KB, 1MB, or 8MB. The first sixteen extents will all be 64KB in size. When the seventeenth extent is allocated, Oracle decides that it had the wrong extent size and allocates a larger, 1MB extent. After sixteen 1MB extents have been allocated, future extents will be 8MB in size. If you create an object and specify an initial extent of 1MB, then Oracle skips the 64KB extents and proceeds directly to a 1MB extent. If you create an object and specify an initial extent of 2MB, then Oracle allocates two 1MB extents for you.

When the uniform extent management method is selected, you must specify an associated extent size. When objects are created in the tablespace, they will all have extents that are sized based on this value. The default extent size for a uniform extent managed tablespace is 1M. Here is an example of creating a locally managed tablespace using the uniform space management option:

DATAFILE ‘/u01/oracle/data/DB1/large_tables_01.dbf’

If using uniform extents, it is generally a best practice to create the extent size as an integer multiple of the blocksize for that tablespace. If using automatically allocated extents, the extent sizes are integer multiples of any valid block size.. Further, when sizing datafiles to be used in locally managed tablespace, follow this formula to minimize wasted space used by the bit map in each datafile used to track free blocks.

Size = 64K + (tablespace block_size * N)

Also, in Oracle8i and Oracle9i Release 1, the SYSTEM tablespace could not be created as a locally managed tablespace. Beginning with Oracle9i Release 2, the SYSTEM tablespace could be created as a locally managed tablespace. In fact, if you use the Oracle database creation assistant to create a database in Oracle9iR2 or higher, it will create the SYSTEM tablespace as a locally managed tablespace by default. If the SYSTEM tablespace is a locally managed tablespace, then all of the tablespaces in the database must be locally managed. Dictionary managed tablespaces are not supported when the SYSTEM tablespace is locally managed.

Automatic Segment Space Management

Oracle offers a new method of managing space within the blocks of a tablespace in the form of Automatic Segment Space Management (ASSM), a new feature in Oracle9i. ASSM is enabled via the segment space management clause of the create tablespace command. The segment space management clause allows you to select from the default space management behavior, or to use automatic segment space management through the use of the manual or auto parameters. Let’s look at a summary of these two space allocation methods:

  • MANUAL – This is the default setting when a locally managed tablespace is created. This setting indicates that Oracle should use freelists to manage free space in segments.
  • AUTO – This option enables ASM for the tablespace being created. Oracle will use bitmaps to manage free space in segments. These bitmaps allow the Oracle engine to manage the free space more easily and automatically. 

If you choose to use ASSM, there are some restrictions that you should be aware of. These restrictions include:

  1. ASSM cannot be specified for the SYSTEM tablespace.
  2. ASSM can only be associated with permanent locally managed tablespaces, not temporary tablespaces.
  3. LOBS cannot be stored in tablespaces configured to use ASSM.
  4. If extent management is set to local uniform, the extent size must be at least 5 tablespace blocks.
  5. If extent management is set to local autoallocate and the tablespace block size is 16K or larger, the Oracle engine automatically allocates extents with a minimum size of 1M.

Due to the architecture and the sharing of data between instances in a Real Application Cluster environment, ASSM should be considered in the strategy to obtain optimal performance.

Migrating From Dictionary Managed Tablespaces

The dbms_space_admin package contains several procedures for managing locally managed tablespaces including functionality to convert a dictionary-managed tablespace into locally managed tablespaces. Be cautious when converting your dictionary-managed tablespace, following the correct procedures outlined in the Oracle documentation. Many DBAs prefer to move database segments to a locally managed tablespace than to convert a dictionary managed tablespace to locally managed. Indexes can be moved with the alter index rebuild statement. Tables can be moved with the alter table move command. You should always backup your database before converting any dictionary-managed tablespace to a locally managed tablespace.

Table 1:  Description of the procedures in the package from Oracle 10g

Procedure Description
SEGMENT_CORRUPT Marks the segment corrupt or valid so that appropriate error recovery can be done.
SEGMENT_DROP_CORRUPT Drops a segment currently marked corrupt (without reclaiming space).
SEGMENT_DUMP Dumps the segment header and extent map of a given segment.
SEGMENT_VERIFY Verifies the consistency of the extent map of the segment.
TABLESPACE_FIX_BITMAPS Marks the appropriate data block address range (extent) as free or used in bitmap.
TABLESPACE_FIX_SEGMENT_STATES Fixes the state of the segments in a tablespace in which migration was aborted
TABLESPACE_MIGRATE_FROM_LOCAL Migrates a tablespace from locally managed to dictionary-managed.
TABLESPACE_MIGRATE_TO_LOCAL Migrates a tablespace from dictionary-managed to locally managed.
TABLESPACE_REBUILD_BITMAPS Rebuilds the appropriate bitmap.
TABLESPACE_REBUILD_QUOTAS Rebuilds quotas for given tablespace.
TABLESPACE_RELOCATE_BITMAPS Relocates the bitmaps to the destination specified.
TABLESPACE_VERIFY Verifies that the bitmaps and extent maps for the segments in the tablespace are in sync.

Summary of the Advantages of Locally Managed Tablespaces

To summarize, there are several advantages to using locally managed tablespaces, including:

  1. Performance improvements due to elimination of recursive operations on the data dictionary required during space management in dictionary managed tablespaces. These operations include a reduction in redo and undo generation.
  2. Using the AUTOALLOCATE clause simplifies space allocation because the Oracle engine automatically controls the appropriate extent size.
  3. The use of the locally managed tablespaces removes any issues with regards to free space fragmentation within a given tablespace.
  4. Readable standby databases are allowed, provided that the temporary tablespaces are locally managed.