Skip to content

Personal tools
You are here: Home » Oracle » Oracle Articles Archive » Locally Managed Tablespaces
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548

Locally Managed Tablespaces

by Jonathan Lewis

Locally managed tablespaces were introduced in Oracle 8i, and have slowly been gaining popularity. However, the take-up is still a little slow. This article describes what are locally managed tablespaces, why they are good, and offers strategies for using them.

Tablespaces Past and Present

A tablespace is a logical unit of storage. It can span many data files, and contain many data segments. The available space in a tablespace is broken up into extents, and each data segment is made up of one or more extents, in which an extent is simply a contiguous section of a single data file. Typically, a data segment corresponds to a self-contained and cohesive collection of data (such as a table, or an index partition) that has some meaning to an end user.

This outline of a tablespace immediately introduces two space management issues. First, which extents belong to which segment; secondly, which extents are in use and which are available. The methods of addressing the first issue have not changed (much) in recent years, but Oracle Corp. has introduced locally managed tablespaces (LMTs) to address problems associated with the second issue.

The Past

Historically, space management in a tablespace was handled through a couple of tables, uet$ (used extent table) and fet$ (free extent table).

When you needed to allocate some space to a segment, Oracle would search fet$ for an entry describing an extent of an appropriate size in the correct tablespace. In fact, there were a number of complicated little strategies that Oracle used in this search that could take some time to complete — but eventually, Oracle would delete (or modify) a row in fet$ and insert a row into uet$.

Similarly, when you freed up an extent (by dropping a table, say) Oracle would delete a row from uet$ and perform a row-insert or modification on fet$.

In fact, the entire process could also require some changes to be made to a row in seg$ (the table describing data segments) and tsq$ (the table describing space quotas that had been allowed to users). Moreover when you added an extent to a segment, a map in the segment's first block (the segment header block) had to be updated to record the fact that the extent was part of the segment.

So, all the work regarding space management for all the tablespaces in the entire database focused on two critical tables in the data dictionary (hence, dictionary managed tablespaces or DMTs.) Unless the DBA really knew what was going on and was totally in control of the system, this could cause problems. There were three main reasons why problems could appear.

First, Oracle Corp. had decided to protect all space management operations under a single space transaction enqueue ("ST" lock), rather than one lock per tablespace. So if a number of jobs were making heavy demands for space management, they could easily end up queueing up for the lock and wasting processing time.

Secondly, Oracle Corp. effectively encouraged DBAs to generate a demand for space management tasks by introducing various segment-level storage parameters (such as initial, next, pctincrease) that promised a spurious degree of precision in storage requirements but defaulted to values that guaranteed that space management would be poor.

Finally, there was enough ignorance and uncertainty in the marketplace that it was easy for junior DBAs to follow procedures that more or less guaranteed that if something could go wrong, it would go wrong. All the problems relating to DMTs can be avoided — so long as someone gives you enough time to find out how they really work (and does the modern DBA ever get the time they need?).

The Present

An LMT is responsible for its own space management. In a clean system, every file in a tablespace is sliced into equally-sized chunks (with the exception of the first 64K of the file, which is used to store a bitmap identifying which of the other chunks are currently in use). Each bit in the bitmap corresponds to a chunk in file — if a bit is set, the chunk is in use, and if a bit is clear, the chunk is free. Figure 1 shows a schematic of a newly created tablespace containing a single file, and the state of that file after some object creation and dropping has occurred.

Figure 1: . A Clean LMT (upper) and a partly used LMT (lower).

You can specify the size of the file, and the size of the chunks to be used in the file. For example, consider the script:

    create tablespace demo_01
    datafile 'c:\oracle\oradata\D9202\demo_01.dbf'
    size 102464k
    extent management local
    uniform size 1024K

This creates a tablespace with a single file (with a very fussy size declaration), which is sliced up into exactly 100 chunks of 1024K, but has an extra 64K specified to cater for the file's bitmap. If we query the dba_free_space view to find out about the free space in this tablespace, we will find that Oracle reports exactly 104,857,600 bytes. When we try to allocate an extent, we will find that the extent will be exactly one chunk — under uniform size management, one chunk equals one extent.

A common problem with LMTs is that DBAs declare the file size without catering for the bitmap space; consequently they "lose" most of an extent's worth of space at the end of the file because the file is just 64K too small to create the last extent. If this happens to you, all you have to do is resize the file to add the missing 64K, and you may suddenly discover a whole extra extent appearing in dba_free_space.

Note: there is an autoallocate option for LMTs that can be used instead of uniform size X. This still slices the file up into uniform chunks (in this case, always at 64K), and uses one bit per chunk. However, instead of equating one chunk with one extent, Oracle will consider past history and available gaps to decide what size extent to allocate. The extent will be one of a limited set of sizes — 64K, 1MB, 8MB, 64MB, or 256MB. For relatively small, simple systems in which there isn't much information available about proper sizing requirements, this can be a minimum fuss mechanism to adopt; but in general, you should stick with uniform sizing.

So what difference do locally managed tablespaces make? Most significantly, whenever you allocate space in an LMT, Oracle does not have to search through a table to find a row that describes a suitable chunk; instead, it just scans the first few blocks of the file looking for the first free bit, and sets it. This is a much more efficient method of finding and allocating space, and has the pleasant side-effect that free space near the start of the file will be preferentially allocated — which may help to keep file sizes small, and eliminate redundant effort in rman backups.

Of course, Oracle still has to worry about the seg$ table and the segment header block, and may still have to update the tsq$ table, but the most labour-intensive part of the operation becomes a lot more efficient. Moreover, instead of using a single space transaction (ST) enqueue to cover the entire database, Oracle uses a new enqueue type — the TT enqueue — and allows one TT enqueue per tablespace to reduce problems of contention due to simultaneous space transactions.

There are trade-offs. It is now much quicker and cheaper to allocate and de-allocate space, but some of the classic reports for summarising free space or used space just got more expensive. Instead of querying a single table (fet$ and uet$ respectively) you now have to visit every file header to get a summary of free space, and every segment header to get a summary of used space. However, performance is not really the issue, and such reports need not be run frequently (I hope).

Where Are the Benefits?

There are always three areas in which a new feature might be of benefit: (a) strategic direction, (b) performance and (c) administrative ease. I shall address each topic in turn.

Strategically, you should be moving your systems to LMTs. Under Oracle 9.2, the database creation assistant will by default create your database with the system tablespace declared as an LMT. If the system tablespace is an LMT, you will not be able to create any DMTs in the database. Clearly, Oracle Corp. expects everyone to migrate to LMTs in the near future — quite possibly, DMTs will cease to exist in Oracle 10 — so it would be a smart move to get the migration over and done with before the next version of Oracle arrives. By the way, even if system is an LMT, you will still be able to use the transportable tablespace mechanism to attach a DMT to the database, but that tablespace will have to remain read-only.

As far as LMTs are concerned, performance is pretty much a non-issue. Although the 'amazing' performance benefit of the bitmap management seems to be a commonly touted reason for switching from DMTs to LMTs, it only takes a couple of minutes thought about when, where, and how often you get this benefit to make you realise that it is pretty irrelevant. Just ask yourself — how often should you be allocating and de-allocating space ? The correct answer is — hardly ever. Consider the commonest occasions:

      • You have allocated a permanent tablespace instead of any form of temporary tablespace as the users' temporary_tablespace (an option that is blocked in V9 with error ORA-12911: permanent tablespace cannot be temporary tablespace). Consequently, all sorting, hashing, temporary LOBs and temporary tables get dumped into permanent data segments instead of using the sort extent pool. This could result in a performance problem that could be reduced somewhat by using LMTs, but this isn't an LMT/DMT issue, it is a temporary/permanent issue.
      • You have allocated a tablespace of contents type temporary for the users' temporary_tablespace, but the extent size you have allocated is extremely small and some sort operations push the extent demand up to tens of thousands, or even hundreds of thousands, of extents. The next time you restart the database, smon runs at 100 percent CPU for ages with a serious blocking effect on most database activity. This could result in a performance hit that could be reduced dramatically by using LMTs, but this is generally an administrative error, not an inherent performance issue. Admittedly, a user's temporary tablespace has to cope with temporary Lobs, temporary tables, sorting and hashing, and these uses may not be compatible. Consequently you may make a deliberate decision to accept this issue — in which case, you definitely do need LMTs for your temporary tablespaces.
      • You have created several important, high-volume data objects with a small initial and next extent, and a pctincrease of one (following a well-known and frequently quoted piece of misdirection). Consequently, you have many objects that keep allocating extents because each extent request is for a small extent that is soon filled. Moreover, each request is for an odd-sized extent, and therefore typically requires close to maximum work before Oracle decides how to allocate it. This could result in a performance hit that could be reduced significantly by using LMTs, but it is an administrative error, not an inherent performance issue.
      • Your application frequently creates and drops tables on the fly to store transient results. This results in high-stress activity on the space management system. This strategy probably will result in a performance hit, but it is a design error, not an inherent performance error. However, for third-party applications in which you can't get the error corrected, this is the one case when the normally marginal performance benefit of LMTs could be a necessary and important damage-limitation exercise (in the short term).

All the above "performance threats" can be avoided, or minimised, without resorting to LMTs, and many DBAs have been taking the necessary steps to avoid them for many years. There is a well-known paper on the topic available through Metalink or OTN ("How to Stop Defragmenting and Start Living"), but in short:

      1. Don't use storage clauses with objects; always use tablespace defaults.
      2. Set pctincrease = 0 as the tablespace default.
      3. Set initial = next as the tablespace default.
      4. Set the minimum extent clause on tablespaces, to match the initial/next.
      5. Put objects in tablespaces that are appropriate for the expected object size.
      6. Don't export with compress = y if you plan to recreate tables from an import.
      7. Make sure you understand the requirements for temporary space, and declare and allocate (multiple) temporary tablespaces accordingly
      8. Avoid using permanent tables for transient data - look at global temporary tables

So finally, we come to the administrative benefits. Why do LMTs help DBAs keep control of their databases? Essentially, the answer comes back to the paper on defragmenting — the advice it gives is good, and if you switch to uniform-size LMTs, the advice it gives is effectively imposed and enforced at the database level.

If you examine the list above, steps 2, 3, and 4 are covered automatically and unbreakably when you create your tablespaces as locally managed with uniform size — every extent in a tablespace will be the same size. Also, steps 1 and 6 become pretty irrelevant: whatever accidents you have on creating or importing objects with unsuitable storage clauses, Oracle complies with the spirit of the request, but ignores the details of the request by allocating extents according to the tablespace definition. Consequently, much of the strategy that good DBAs have struggled to enforce for many years happens by default with LMTs. And the key word is "struggled." Despite the best efforts of DBAs, it was still possible for things to go wrong with DMTs — with uniform sized LMTs, every extent is forced to be the same size, and no one can break the pattern.

But why is it so convenient to force every extent in the tablespace to be the same size? (And at this point, you may appreciate my earlier comment about avoiding autoallocate LMTs, which allow for half a dozen sizes of extents.) The reasons are, first, ease of monitoring space; secondly, convenience of data packing, and third, reliability of object rebuilds.

Do you have a complicated little script for working out whether or not the next extent for any object in the database will be able to find a large enough space in the right tablespace? If you use uniform LMTs, then this script simplifies to, "Is there any freespace in the tablespace; if so, then it is usable." You could even go so far as to base your reports on a couple of very simple queries:

Rem Find out how many objects per tablespace
Rem Find the unit size for each LMT
Rem Find out the free space per tablespace

select tablespace_name, initial_extent 
from user_tablespaces
where extent_management = 'LOCAL'
and allocation_type = 'UNIFORM' 
-- you might include 'SYSTEM' 

select tablespace_name, count(*) 
from dba_segments 
group by tablespace_name

select tablespace_name, sum(bytes)
from dba_free_space
group by tablespace_name

By making [some localised variant of] these three queries into /*+ no_merge */ in-line views and joining them with a suitable outer join, you could, for example, produce a report showing how many data segments you have per tablespace, and how many of them could extend simultaneously without causing a problem.

Similarly, you could start with a simple query such as:

     tablespace_name, segment_name, partition_name, extents
from dba_segments

You could then combine this with the first of the three queries given previously to capture a daily, or weekly, list showing number of extents per object. This gives you the option for producing a "diff" report of segment growth that can be used to predict future space requirements. Once you have a mechanism that allows you to equate number of extents with size of object, it is so much easier to recognise patterns.

The goal, then, is to ensure that you pick uniform sizes that make it possible to produce warning reports and predictive reports that are useful. And a key feature of usefulness means they should appear only when they have something important to say, and then don't hide it away under a huge volume of trivia and irrelevancy.

The guidelines in the article on defragmentation are good ones to apply to LMTs. Identify objects by such attributes as application, style of use, function, and so on, and finally by size. Typically, you might choose three or four representative sizes for your objects — such as "small," "medium," "large," and "enormous" — and then define tablespaces to match each of these sizes. (I tend to work in multiples of 8 or 16, so for a set of four sizes, and might set up tablespace with uniform sizes of 64K, 512K, 4MB, 16MB). You then allocate objects to tablespace on the basis that fairly static objects should have perhaps 1 to 16 extents, whereas regularly growing objects should add one extent every couple of months. The net effect of this strategy is that you tend to size your database suitably, and don't get any nasty surprises as the data grows.

Of course, mistakes do happen, and your first estimates may put an object in the wrong size of tablespace. It's quite easy to move objects from one tablespace to another — and when you do so, you won't have any problems fitting an object into a tablespace. If there is enough space for an object in a tablespace, then all that space will be usable. Remember the bad old days when you could have 100MB of free space in a tablespace, but be unable to import a 51MB table because the 100MB was made up to two disjoin holes of 50MB each? This simply doesn't happen with LMTs. All holes are the same size, and every object is automatically created as a series of chunks that exactly match the holes. When you move a 24MB table from the "64MB tablespace" to the "1MB tablespace," it doesn't need (and can't have) a single 64MB extent; it automatically arrives as 24 extents of 1MB.

For data warehouse users, the convenience and reliability of space usage also makes it easier to develop a strategy of moving and packing data just before making it read only. (And Oracle 9.2 offers a tremendous added advantage for read-only table data with the compress option for data.) Just before you make a data set (for example, the partitions for last month) read only, you can move and compress the data, rebuild the indexes and trim the containing files to the minimum possible size. And you can do this with a level of convenience and confidence that was not possible with DMTs.

In fact, you could choose to move the objects into tablespaces of the 'wrong' uniform size since there may be an optimum extent count that is a better match for your use of multiple devices, multiple files and parallel execution. The options for proactive space management and performance enhancement through space management become quite interesting when you switch to LMTs.


So what do we really get from LMTs?

      • The solution to one special issue that Oracle forces on us because all the function of temporary storage is met by a single tablespace per user.
      • Help with avoiding a couple of administrative errors.
      • Some important assistance in space monitoring and management.

Should we use LMTs? Absolutely — anything that helps you to eliminate complexity and risk of error, especially in mundane, but time-consuming tasks, is a good thing. And especially if using them introduces a couple of performance-related benefits.


Jonathan Lewis is a freelance consultant with more than 17 years' experience in Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of Practical Oracle 8i - Building Efficient Databases published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, tutorials, and seminars can be found at, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups.

Contributors : Jonathan Lewis
Last modified 2005-02-24 10:49 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone