Space Management in Tablespaces

Tablespace extents can be managed either by the traditional (and still default) method, wherein the information concerning free space information for the tablespace resides in the data dictionary, or through use of locally maintained bitmaps. After you have chosen a space management method, it is not easy to alter this method later.

Locally Managed Tablespaces

A tablespace has the ability, in Oracle 9i, to manage its own extents in a bitmap inside each data file. It uses this mechanism to keep track of the free and used status of the blocks in the data file. Whenever a block is used or freed for reuse, Oracle changes the value of the bitmap to show the new status of the block.

Locally managed tablespaces store their free extent information in bitmap segments within the tablespace. Each bit in the bitmap corresponds to a block or to a group of blocks, and the value of the bit indicates that the blocks are free or used.

Because locally managed tablespaces control and track their free space usage within a bitmap within the data file, contention is significantly reduced on the data dictionary tables, and no undo is generated when space is allocated or deallocated. Recursive space management operations that occur in dictionary managed tablespaces when consuming or releasing extents result in contentions on data dictionary tables. Locally managed tablespaces automatically track adjacent free space, meaning that there is no longer any need to coalesce free space because it is all managed within the data file itself.

To create a locally managed tablespace, the LOCAL option of the EXTENT MANAGEMENT clause needs to be specified as follows:

 EXTENT_MANAGEMENT [DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]] 

The options for the EXTENT MANAGEMENT clause are explained in the following list:

• DICTIONARY specifies that the tablespace will be managed using the data dictionary tables.

• LOCAL specifies that the tablespace will be managed using a bitmap. If you specify LOCAL, you cannot specify a DEFAULT storage clause or MINIMUM EXTENT.

• AUTOALLOCATE specifies that the tablespace is system managed. You cannot specify an extent size if AUTOALLOCATE is specified, and AUTOALLOCATE is the default.

• UNIFORM specifies that the tablespace is going to be managed with uniform extent size, with each extent the SIZE of bytes. (You can use either K for kilobytes or M for megabytes for the SIZE specification.) The default is 1 megabyte.

You can use the EXTENT MANAGEMENT clause in the CREATE TABLESPACE command for any tablespace that is not the SYSTEM tablespace (which you only indirectly create, anyway). For a temporary tablespace, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command.

Dictionary Managed Tablespaces

Dictionary managed is the default method for creation of tablespaces. Free extents associated with the tablespace are recorded and managed in the data dictionary tables. Every segment in the tablespace, if the tablespace is dictionary managed, can have its own different storage clause. Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or deallocated in relation to the tablespace. Coalescing is required in frequently updated segments in dictionary managed tablespaces.

Now that we have a handle on dictionary versus locally managed and SYSTEM versus non-SYSTEM tablespaces, we will look at how to change the size and storage settings in tablespaces.