Oracle Locally Managed Tablespaces

A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

Locally-managed tablespaces have the following advantages over dictionary-managed tablespaces:

• Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table.
   
• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally-managed tablespace. The LOCAL option of the EXTENT MANAGEMENT clause specifies this method of space management in various CREATE commands. For a temporary tablespace, you can also specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command.

Note: Before Oracle 9.2.0, the SYSTEM tablespace could not be locally managed. There has been conflicting information published on this. Initially, Oracle’s 8i documentation stated that you could do this, but they have since corrected this. If you try to create the SYSTEM tablespace as locally managed, Oracle will generate an ORA-600 message. Oracle 9.2.0 introduced the ability to create a locally managed SYSTEM tablespace. In future versions of Oracle, locally managed will be the only tablespace management method.

Why Use Locally Managed Tablespaces?

There are several benefits to using locally managed tablespaces:

• Improves concurrence of space operations
• Space is allocated and deallocated by changing the bit values (0 to 1 for allocation, 1 to 0 for deallocation).
• Eliminates recursion during space management operations
• Supports temporary tablespace management in standby databases
• Reduces user reliance on the data dictionary
• Necessary information is stored in segment headers and bit map blocks.
• Creating a Temporary Tablespace

Example

Say we want to create a data tablespace for the accounts receivable (AR) application in our accounting package. The database for our AR application is called ‘ORACTP’ short for Oracle Accounting Production database. First let’s look at the command to create a dictionary managed tablespace with an initial 500 megabyte datafile with autoextention to a maximum size of 1 gigabyte (1024 megabytes). The tables in our AR application will hold a fairly large amount of data. So, just in case our developers forget to size their tables let’s size the default storage to INITIAL 10M NEXT 1M PCTINCREASE 10.

CREATE TABLESPACE ar DATAFILE '\ORACLE1\ORACTP\data\oractp_ar01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 200M MAXSIZE 1024M
DEFAULT STORAGE (INITIAL 10M NEXT 1M PCTINCREASE 1)
PERMANENT
ONLINE
LOGGING;

The PERMANENT, ONLINE and LOGGING clauses have been included for illustration only, they are the default if nothing is specified. Why is a PCTINCREASE value of 1 specified? If PCTINCREASE is set to zero the SMON process will not coalesce free space. If PCTINCREASE is not specified, it will default to 50%; therefore, specifying it at a low value is suggested.

For this example, because we want the extents to be locally managed due to the level of dynamic allocation which will happen, the CREATE TABLESPACE clause will change so that we no longer specify the DEFAULT STORAGE clause and instead we use the EXTENT MANAGEMENT clause with the LOCAL option. If we want to ensure that uniform extents are generated then we can specify the UNIFORM clause as well. In the example below we'll use 2 megabyte LOCAL UNIFORM extent management.

CREATE TABLESPACE ar DATAFILE '\ORACLE1\ORACTP\data\oractp_ar01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 200M MAXSIZE 1024M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
PERMANENT
ONLINE
LOGGING;

In the early days of locally managed tablespaces, many DBAs would only use the UNIFORM allocation method. Many DBAs stayed away from letting Oracle automatically determine the extent sizes with the AUTOALLOCATE allocation method. The fear of using AUTOALLOCATE has been significantly reduced as of late. In fact, many DBAs are now indicating that AUTOALLOCATE is the preferred method of space allocation in locally managed tablespaces for most situations.