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 following series of images below to see how to create a locally managed tablespace.
Steps to creating Locally Managed Databases in Oracle
1) To create a locally managed tablespace, Oracle has provided parameters in the CREATE TABLE command. 
CREATE TABLESPACE tablespacename DATAFILE
  'datafilename' [SIZE n]
 [AUTOEXTEND { OFF | ON }]
 [EXTENT MANAGEMENT 
 {DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM
  [SIZE]] } ];

1) To create a locally managed tablespace, Oracle has provided new parameters in the CREATE TABLE command. The EXTENT MANAGEMENT clause can be used in place of the usual storage specifications (such as minimum extents and default storage space.) If you choose DICTIONARY, the tablespace is managed as usual, using the data dictionary. If you choose LOCAL, the tablespace is managed as usual, using the data dictionary. If you choose LOCAL, the tablespace becomes a locally managed tablespace. If you leave out the EXTENT MANAGEMENT clause, the tablespace is by default a normal, dictionary-managed tablespace.

2) Here is an example of a locally managed tablespace. The tablespace will be created with uniform extents that are 25 MB in size. 
CREATE TABLESPACE localtbsp_01 DATAFILE
  'data_0101.dbs'
 AUTOEXTEND ON
 EXTENT MANAGEMENT 
 LOCAL UNIFORM SIZE 25M;

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 UTOALLOCATE parameter, as shown in this example. 
CREATE TABLESPACE localtbsp_02 DATAFILE
  'data_0102.dbs' SIZE 100M
 EXTENT MANAGEMENT 
   LOCAL AUTOALLOCATE;

3) As alternative to specifying uniform extents is to specify the AUTOALLOCATE parameter, as shown in this example. Tell the databse what size the initial extent will be, and the tablespace will decide the best size for all subsequent extents. The minimum extent size allocated this way is 64 K.

EXTENT MANAGEMENT clause for Oracle 19c

In Oracle 19c cloud-enabled databases, the `EXTENT MANAGEMENT` clause is still relevant and commonly used to specify how extents are managed within locally managed tablespaces (LMT)[1]. This clause is used instead of traditional storage specifications like `MINIMUM EXTENT` or `DEFAULT STORAGE`, which were more commonly associated with older, dictionary-managed tablespaces.
Extent Management in Oracle 19c For locally managed tablespaces, the `EXTENT MANAGEMENT` clause offers two primary options:
  • UNIFORM: All extents are of a fixed size, specified in the clause. This option simplifies storage management by ensuring that all extents are the same size.
  • AUTOALLOCATE: Oracle automatically manages the extent sizes, which can start small and gradually increase as needed. This option is more common and efficient for managing tablespaces dynamically.

Cloud-Enabled Database Considerations In Oracle 19c, particularly in cloud environments like Oracle Cloud Infrastructure (OCI) or Autonomous Database, locally managed tablespaces are preferred due to:
  • Automated management: The AUTOALLOCATE option is often used in cloud settings to optimize storage allocation and minimize manual intervention.
  • Improved performance and scalability: Locally managed tablespaces with extent management improve performance by using bitmaps to manage free space, making them ideal for cloud workloads.

Conclusion The `EXTENT MANAGEMENT` clause is still an essential feature for locally managed tablespaces in Oracle 19c, especially for cloud-enabled databases. It replaces older storage specifications and provides efficient and scalable space management suitable for modern database environments. The use of `AUTOALLOCATE` is particularly favored for its convenience and optimization capabilities in cloud scenarios.

Creating Locally Managed SYSTEM Tablespace

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. The COMPATIBLE initialization parameter must be set to 9.2 or higher for this statement to be successful. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary-managed SYSTEM tablespace. Dictionary-managed tablespaces are deprecated. A locally managed SYSTEM tablespace has AUTOALLOCATE enabled by default, which means that the system determines and controls the number and size of extents. You may notice an increase in the initial size of objects created in a locally managed SYSTEM tablespace because of the autoallocate policy. It is not possible to create a locally managed SYSTEM tablespace and specify UNIFORM extent size. When you create your database with a locally managed SYSTEM tablespace, ensure that the following conditions are met:
  1. A default temporary tablespace must exist, and that tablespace cannot be the SYSTEM tablespace. To meet this condition, you can specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement, or you can omit the clause and let Oracle Database create the tablespace for you using a default name and in a default location.
  2. You can include the UNDO TABLESPACE clause in the CREATE DATABASE statement to create a specific undo tablespace. If you omit that clause, Oracle Database creates a locally managed undo tablespace for you using the default name and in a default location.

  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.

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.

[1] locally managed tablespaces (LMT): Locally Managed Tablespaces (LMTs) are a type of tablespace in Oracle databases that manage their own extents by maintaining a bitmap in each data file to track free and used blocks. This approach eliminates the need to store extent information in the data dictionary, reducing contention and improving performance for space allocation and deallocation operations.

SEMrush Software 3 SEMrush Banner 3