Tablespace Storage Management

A Tablespace is a logical storage unit that is used to store database objects like tables, indexes, views etc., A tablespace contains one or more data files.

The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways.

Prior to 8i, tablespaces was managed by dictionary, which means tablespace use the data dictionary to track free space and extent allocation information. Data Dictionary contains some tables like sys.uet$, sys.fet$ etc., which maintains those information. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. This updation on data dictionary causes to generate rollback information. This incurs at least some additional overhead for storage management.

So the tablespaces that are managed by dictionary are considered as Dictonary Managed Tablespaces (DMT).
So let us see the disadvantages with the DMT’s –

1. Generates Redo
2. Need to periodically coalesce free space
3. contention on data dictionary tables

Starting with Oracle8i, Oracle began giving us options to automate the management of objects at the tablespace level. As of Oracle 8i the EXTENT MANAGEMENT clause was introduced into the CREATE TABLESPACE statement allowing extent management to be LOCAL or DICTIONARY.

In contrast, Locally Managed Tablespaces (LMT) do not use the data dictionary to track free space and extent allocation. Instead, information to manage segments and blocks is kept in bitmaps in each datafile to keep track of the free or used status of blocks in that datafile. 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.

With LMTs, extent sizes can be managed only at the tablespace level, not the object level. You cannot create an object and use a STORAGE clause to specify the extent size other than what has been defined for the tablespace.

Let us see the benefits of using LMTs:

1. Eliminate the need of frequently coalescing free space and 
adjacent free extents will be seen as one large block of 
free space.

2. The need for re-organization is eliminated. 

3. No Fragmentation.

Basically in LMT, there are 3 types in which extents can be allocated –

Automatic Extent allocation, which sizes extents automatically. Oracle assign the appropriately sized extents to objects. Oracle will allocate larger extents to objects as they grow. Oracle allocates 64KB for INITIAL eventough you specify smaller initial value, and ignores the values you specify for NEXT, MAXEXTENTS, and PCTINCREASE. If you set INITIAL value more than 64KB, Oracle will allocate enough extents to give you at least the amount of space you asked for.

create tablespace testtbs datafile 'testtbs01.dbf' 
size 50M extent management local autoallocate;

Uniform Extent Allocation, you specify an extent size when you create the tablespace, and all extents for all objects created within that tablespace will be that size. A tablespace with uniform extent sizes will not have any fragmentation.

create tablespace testtbs datafile 'testtbs01.dbf' 
size 50M extent management local uniform SIZE 1M;

User Extent Allocation, In this LMT behaves as DMT, allocating extents as per the storage clause provided with the object or defaulted at tablespace level. DMTs that are converted to LMTs fall under this type.

In addition, LMTs offer another improvement in Oracle 9i: Automatic Segment Space Management (ASSM), which introduced SEGMENT MANAGEMENT clause into the CREATE TABLESPACE statement, when this set to auto Oracle manages automatically the segements within the tablespaces.

ASSM takes total control of the parameters FREELISTS, FREELIST GROUPS, and PCTUSED. This means that Oracle will track and manage the used and free space in datablocks using bitmaps for all objects defined in the tablespace for which it has been defined.

create tablespace testtbs datafile 'testtbs01.dbf' size 100M
extent management local -- enable LMT 
segment space management auto; -- enable ASSM

Thus we have seen that there are lot of advantages with LMT compared to DMT and so Oracle made LMT as default Extent Management type. By default, Oracle Database sets all newly created user tablespaces to be locally managed. In a database with a locally managed SYSTEM tablespace, you cannot create dictionary-managed tablespaces. However, if you execute the CREATE DATABASE statement manually and accept the defaults, then the SYSTEM tablespace is dictionary managed. You can migrate an existing dictionary-managed SYSTEM tablespace to a locally managed format. Once the SYSTEM tablespace is migrated from DMT to LMT, we cannot revert that back to DMT again. It’s a one-way.

Let us see the possible ways to transit one form of extent management to other –

Transition Methods

DMT to LMT
=========

You can perform in two ways –

1. Create a new tablespace with Extent management local and move all 
the objects that is residing in the old DMT tablespace to the newly 
created one and dropping the old DMT.

2. Using PL/SQL supplied package, DBMS_SPACE_ADMIN , that is used 
to migrate tablespace from one form to other.

a.) To migrate from DMT to LMT

BEGIN
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(
tablespace_name => 'TBS_NAME'); 
END;

b.) To migrate from LMT to DMT

BEGIN
EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL(
tablespace_name => 'TBS_NAME'); 
END;