Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 30, 2004

Transitioning to Locally-Managed Tablespaces

By Jim Czuprynski

Synopsis. Oracle 8.1.6 offered a new format for tablespace storage management - locally-managed tablespaces - that offers significant improvements in tablespace performance and free space management. Oracle 9i Release 2 improved upon segment storage in locally-managed tablespaces with the Automatic Segment Storage Management option. This article delves into the advantages of using locally-managed tablespaces over dictionary-managed tablespaces and suggests practical methods to achieve the transition.

One of my production databases had been created under Oracle 8i just a few weeks before I transitioned into the role of Oracle DBA. Our predecessor DBA had left our company just before we decided to use to Microsoft Windows 2000 Advanced Server instead of Linux Red Hat as our operating system, and my boss had inherited the task of managing the switchover. At the time, I was admittedly green about best practices for tablespace configuration techniques.

A few months later, when I migrated the 8i database to the Oracle 9i environment, once again I was under a severe time crunch -- just one weekend between Christmas and New Year's! -- to accomplish that transition. At that time, I had little time to investigate one of the best new features of Oracle 9i Release 2, automatic segment space management, and had simply migrated the database in place with its dictionary-managed tablespaces.

Enter a new colleague and new experiences. During his orientation to our database environment, my new partner expressed surprise at how long our nightly database backups took for this particular database, which contains approximately 45GB of used blocks. He estimated a time frame of approximately 45 minutes; however, our current database backups were taking over 2 = hours. He also noticed that the tablespaces' datafiles had been created at the maximum size possible on each spindle.

We therefore set out to experiment if locally-managed tablespaces would make a significant difference in database performance, especially for nightly backups and exports.

Dictionary-Managed vs. Locally-Managed Tablespaces

First, let's review the differences between these two tablespace types. As its name implies, information about the storage capacity of dictionary-managed tablespaces (DMTs) is retained in the database's data dictionary. This incurs at least some additional overhead for storage management, since data dictionary tables in the SYSTEM tablespace record all information about which blocks are free, how much free space each block has, and when a block can be INSERTed into, based on settings for each table's PCTFREE and PCTUSED parameters.

Locally-managed tablespaces (LMTs) overcome this overhead by storing free list information in special bitmap entries in each datafile of the tablespace. The bitmap entries track whether or not a block is free or in use, and Oracle automatically updates the bitmap entries whenever an extent is freed or allocated. Since this information no longer needs to be written to a data dictionary table, the rollback entries normally written are eliminated.

Automatic Segment Space Management

LMTs therefore, essentially manage their own extents automatically, while in a DMT there is a constant need to coalesce free extents (i.e. adjacent extents that contain free blocks). In addition, LMTs offer another improvement in Oracle 9i: Automatic Segment Space Management (ASSM). Setting the SEGMENT SPACE MANAGEMENT clause of the CREATE TABLESPACE statement to AUTO notifies Oracle to manage automatically the storage space within the segments within that tablespace. This feature helps a tablespace to avoid recursive space-management operations that may occur in a DMT when the consumption or reduction of space in the DMT requires the data dictionary table that is managing the extents to expand or contract.

When a tablespace is created under Oracle 9i, note that LMT is the default setting, and in later versions of Oracle, the concept of the DMT is being phased out. Note, however, that ASSM must be specified (the default setting is MANUAL) and that the COMPATIBLE parameter of an Oracle 9i database may need to be adjusted upward to use it.

Transition Methods

We found two basic methods worked for transitioning from a DMT to an LMT environment:

Method 1: Migrate to Newly-Created LMTs

If sufficient disk space exists, create new locally-managed tablespaces and migrate tables and indexes to those tablespaces.

Creating New Tablespaces. We used the CREATE TABLESPACE command to create the new LMTs. We also made sure that the datafiles were now autoextensible instead of being sized statically, but reduced in their initial size to accommodate the total space required for all table and index segments being migrated, which significantly reduced the size of most of our datafiles.

Here is an example intended to migrate some of the HR schema's objects to a newly-created LMT with automatic segment space management:

   DATAFILE 'g:\oracle\oradata\zdcdb\hr_lmpt.dbf' 
SIZE 512M 

Migrating Tables. Moving tables to their new locations was relatively simple via the ALTER TABLE MOVE command. For example:

ALTER TABLE hr.departments MOVE TABLESPACE hr_lmpt;

Special Considerations for LOB Datatypes. One important consideration to note: If the table contains LOB datatypes (BLOBs, CLOBs, or NCLOBs), these LOB segments will need to be moved separately to an appropriate LMT for LOB storage:

ALTER TABLE hr.employee_pictures 
MOVE LOB(picture) 
STORE AS hr_employee_picture_lob (TABLESPACE hr_lobs); 

Migrating Indexes. To move indexes, you will need to use the ALTER INDEX REBUILD command. Note that statistics can be recalculated for the index via the COMPUTE STATISTICS directive:

ALTER INDEX hr.dept_pk_idx 
TABLESPACE dept_lmt 

Dropping Old DMTs. Once all objects have been migrated from the original DMTs, these tablespaces can be removed from the database:


Method 2: Migrate DMTs to LMTs

If insufficient disk space exists, consider using the DBMS_SPACE_ADMIN PL/SQL supplied package to switch all DMTs to LMTs. The advantage of this method is that no migration of data from source DMT to target LMT need occur:

tablespace_name => 'HR_DMPT'); 

Likewise, an LMT can be migrated back to a DMT with this package:

tablespace_name => 'HR_DMPT'); 

I must admit that I have only experimented briefly with this approach, and I have yet to apply it directly to a full database migration. One major point, whichever method you choose. Beware that once the SYSTEM tablespace is switched from a DMT to an LMT, it is a one-way ticket: Oracle will prohibit the creation of any additional DMTs in the database. Therefore, it is probably a good idea to leave a SYSTEM tablespace that is already a DMT alone, especially if you decide to revert to DMTs for one or more converted tablespaces.


With our new tablespace configuration in place, we performed comparative testing and measured the outcome. I have to admit that I was pleasantly surprised with the results:

  • With the new tablespace configuration and freshly-migrated table and index segments, it now takes only 41 minutes to complete a nightly RMAN backup scheme -- a complete Incremental Level 0 backup of the database - as compared to 150 minutes the same scheme for DMTs, a 72% reduction.
  • With the new configuration, the nightly export scheme, which involves exporting every row in the database in either Direct or Conventional mode, only takes 160 minutes vs. 216 minutes with DMTs, a 26% reduction.
  • The migration of tables and indexes to their new LMTs had another positive side-effect: The number of migrated rows was significantly reduced, and all indexes were rebuilt, thus giving my users a "clean" database to fill up once again.

While these performance improvements are more likely due to the migration of the segments to their new tablespaces, and the fact that the backups do not have to read through a bunch of empty blocks every time a tablespace is backed up, it is still an impressive improvement worthy of the experiment.


Oracle 9i's locally-managed tablespaces offer distinct advantages over their Oracle 8i cousins, the dictionary-managed tablespaces, and converting from DMTs to LMTs, while not trivial, does offer significant advantages, especially automatic segment space management.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 9i Release 2 documentation for the deeper technical details of this article:

A96524-01 Oracle 9i Concepts, Chapter 3

A96540-01 Oracle 9i SQL Reference, Chapter 11

A96612-01 Oracle 9i Supplied PL/SQL Packages and Types Reference, Chapter 68

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM