Table Space Management   «Prev  Next»

Lesson 3Locally managed tablespaces
ObjectiveCreate locally managed tablespaces.

Locally Managed Tablespaces in Oracle

Managing a tablespace normally requires that the database maintain its data dictionary tables and views whenever the tablespace changes.
For example, the task of inserting a row into a table causes the database to update the statistics on available and used space in the tablespace. Oracle has introduced a new type of tablespace: the locally managed tablespace.
The information that usually resides in the data dictionaries, such as extents, percent used, percent free, and so on, are tracked inside the tablespace itself through the use of bitmaps. Bitmaps manage space allocation very efficiently, so tablespaces using bitmaps are less fragmented and use space more efficiently than those not using bitmaps. In addition, because they require no dictionary access to allocate or update extents, tablespaces using bitmaps are more self-contained.
Look at the SlideShow below to see how to create a locally managed tablespace.

  1. To create a locally managed tablespace, Oracle has provided new parameters in the CREATE TABLE command.
  2. Here is an example of a locally managed tablespace. The tablespace will be created with uniform extents that are 25 MB in size.
  3. As alternative to specifying uniform extents is to specify the AUTOALLOCATE parameter, as shown in this example.

Create Locally Managed

Locally Managed Tablespaces

A locally managed tablespace uses a bitmap stored in each data file to manage the extents.
About Locally Managed Tablespaces: Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps.
Locally managed tablespaces provide the following benefits:
  1. Fast, concurrent space operations. Space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
  2. Enhanced performance
  3. Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.
  4. Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
  5. User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
  6. Coalescing free extents is unnecessary for locally managed tablespaces.

All tablespaces, including the SYSTEM tablespace, can be locally managed.
The DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.

The locally managed tablespace can be used to ease the load of querying and updating your data dictionary views.
This is especially useful when the tablespace contains a table or object that is frequently modified by multiple users.
In the next lesson we will look at how to create transportable tablespaces.