IT Innovation

More Space and More Time


Improvements in tablespace management features mean more free space.

By Jonathan Gennick

January/February 2005


Databases often begin with some data, and very soon they contain lots of it. DBAs therefore spend a good deal of time and energy dealing with the issues of where to place data, how to allow for growth, and how to move data around efficiently as a database evolves. In the Oracle world, a discussion of data growth quickly leads to the word tablespace , which designates the logical structure administrators use to manage the placement and growth of data in a database. Tablespaces, in turn, contain other logical structures such as tables, indexes, and any other type of object that consumes space for data.

Tablespace management is a critical task for any DBA, and successive Oracle database releases have introduced several important tablespace-related enhancements that make a DBA's tablespace management job easier. This article looks at the more significant tablespace enhancements and explains why you should be taking full advantage of them.

Locally Managed Tablespaces

Perhaps the most significant tablespace enhancement was the introduction in Oracle8i of the locally managed tablespace . Prior to this feature, the allocation of extents in a tablespace to individual tables was recorded in the data dictionary by use of dictionary-managed tablespaces . If an insert or an update caused a table to grow, processing of that insert or update had to stop while recursive SQL was issued against the data dictionary to find a free extent to assign to the table. Such recursive SQL might generate rollback data, which—prior to Oracle9i at least—had to be written to a rollback segment, which might itself need to extend, resulting in even more recursive SQL. Additionally, data dictionary space management operations are serialized, meaning that only one could take place at any given time and that single-threading sometimes led to bottlenecks on multiuser systems in which tables were growing quickly and extending often. DBAs thus had to plan carefully to minimize the rate at which tablespace extensions occurred and often battled the latch contention that resulted from too frequently occurring data dictionary operations.

Locally managed tablespaces free DBAs from all of these worries. With locally managed tablespaces, extents are managed through bitmaps inside the tablespaces themselves, completely removing the need to access the data dictionary. This means no more recursive SQL, no more rollback generation, no more single-threaded bottlenecks, and no more data dictionary latch contention and the performance bottlenecks that come with it. Space is allocated after a quick scan of a bitmap in a datafile header. Oracle supports two types of locally managed tablespaces, which are distinguished by the way extents are sized and allocated:

  • Uniform extent allocation uses extents of the same size for all objects within the tablespace.

  • Automatic extent allocation uses a set of extent sizes that are factors of each other, beginning with 64K and moving upward through 1MB, 8MB, and 64MB.

Uniform extent allocation. Uniform extent allocation has long been considered a best practice, because it allows any freed extent in a tablespace, perhaps as the result of a table's being dropped, to be readily reassigned to another object that needs to grow. Without uniform extent sizes, free space in a tablespace would inevitably end up fragmented into a random set of extents of various sizes, and possibly none of those sizes would be adequate for the next table or index that needed to grow. Under those circumstances, you might have a total of 100MB free and yet be unable to allocate 10MB to a table that needed to grow. The end result would be a user error or a failed batch job and a small crisis as the DBA scrambled to find more space, restart failed jobs, and so forth.

To combat fragmentation in dictionary-managed tablespaces, DBAs periodically coalesced free space in tablespaces. Sometimes tablespaces needed to be unloaded and reloaded to leave the free space in one contiguous chunk. Many weekends and nights were no doubt lost to this sort of work.

In the days of dictionary-managed tablespaces, uniform extent allocation was possible to achieve but difficult, because each object in a dictionary-managed tablespace has its own extent size, leaving it up to the DBA to be sure to specify the same size for each object created. Making a mistake was all too easy.

Automatic extent allocation. Automatic extent allocation is intended to be a way to make tablespace management very simple and is especially useful when having a professional, full-time DBA is simply not feasible. The idea is to just dump all your objects into one large tablespace and let the database management system sort out the space allocation. All objects begin with 64K extents. As objects grow, so do their extent sizes, so for very large objects, you have large extents, not an overallocation of 64K extents. Because extent sizes are all multiples of each other, fragmentation of free space is also much less of an issue. For example, a series of sixteen 64K extents can be reclaimed as a single 1MB extent, if necessary.

Locally managed tablespaces free DBAs from all the worries about fragmentation and coalescing just described. Extent size is enforced at the tablespace level. It is not possible to create an object with an extent size other than what the tablespace allows. All extents are the same size, and thus any free extent can be reused at any time by any object in the tablespace that needs to grow. Coalescing of free space is irrelevant, and there's no longer any need whatsoever to unload and reload to pack all free space together.

Many DBAs have no doubt spent many more nights and weekends with their families as a result of locally managed tablespaces.

Default Tablespaces

"Never, ever create user objects in the SYSTEM tablespace!" This is one of the first rules drilled into any new Oracle DBA. The SYSTEM tablespace is for the data dictionary and other system-maintained objects only. To avoid accidentally placing new objects into the SYSTEM tablespaces, new DBAs were often instructed to specify a default tablespace other than SYSTEM for any new user they created, such as in the following:

CREATE USER my_new_boss
IDENTIFIED BY some_password

Unfortunately, it was all too easy to forget to specify a default tablespace when creating a new user, and thus any new objects created by that user would get placed in the SYSTEM tablespace. Oracle Database 10g introduces the concept of a databasewide default tablespace. You need to specify it just once, as follows:


Now any new user you create without a user-specific default tablespace will use the databasewide default. It's a convenience that removes one possible misstep from what you need to worry about when creating new users.

Default Temporary Tablespaces

Temporary disk space is frequently needed for sorting or grouping data in response to ORDER BY and GROUP BY clauses and for building joined result sets. Because temporary disk space is so actively used, by so many users, it's always been a best practice to confine such space to specific tablespaces created for the purpose. You can create a locally managed temporary tablespace by using a CREATE TEMPORARY TABLESPACE command, such as the following:

TEMPFILE '/oradata/db01/
temp01.dbf' SIZE 100M

Before Oracle9i, when you created a temporary tablespace, you had to link it to each user, so that it would be employed for that user's queries:

CREATE USER my_new_boss 
IDENTIFIED BY some_password 

If you forgot the TEMPORARY TABLESPACE clause when creating a user, the SYSTEM tablespace would be the location for all of that user's sorting, grouping, joining, and other temporary disk activity.

The SYSTEM tablespace, however, is not intended to be the location for sorting and other temporary disk operations, nor is it usually sized to accommodate them. The SYSTEM tablespace is also heavily used, and increasing that already high input/output load by adding input/output for sorts and other temporary disk operations risks dragging down performance.

To help you guard against forgetting to assign a default temporary tablespace and prevent abuse of the SYSTEM tablespace, Oracle9i Database introduced the concept of a databasewide default temporary tablespace:


After you run this command, all new users you set up will use the temp temporary tablespace by default; you don't need to remember to specify a temporary tablespace or even remember the name of the default temporary tablespace.

An added administrative bonus is that, assuming that all users default to the systemwide setting, you can easily switch from one temporary tablespace to another.

Temporary Tablespace Groups

Oracle Database 10g introduced the concept of a temporary tablespace group . Rather than having just one temporary tablespace and the possibility that it may become a performance bottleneck, you can define an entire group of temporary tablespaces and spread temporary tablespace input/output across them. To create a group of temporary tablespaces, simply specify the GROUP clause when creating it. Both of the following tablespaces comprise a temporary tablespace group named temp_group01 :

temp01.dbf' SIZE 100M
temp02.dbf' SIZE 100M

Having created a group of temporary tablespaces, you can now make that group the default source of temporary disk space for your database, as follows:


Now, rather than have all temporary input/output go against a single temporary tablespace, the database can distribute that I/O load among all the temporary tablespaces in the group. Temporary tablespace groups are even more of an advantage when you are performing operations in parallel. If you issue an ORDER BY query against a partitioned table and if multiple execution server processes are started in order to process that query in parallel, one for each partition, those execution servers no longer need to all funnel to the same temporary tablespace. Instead, the execution servers will be distributed across all the temporary tablespaces in the active group.

Bigfile Tablespaces

The bigfile tablespace is a new type of tablespace introduced in Oracle Database 10g. As the name suggests, a bigfile tablespace consists of a single, potentially very large file. You can use bigfile tablespaces to create extremely large databases and minimize the number of datafiles a database must manage, which has the additional advantage of reducing your system global area (SGA) memory requirements, because you'll be able to run with a lower value for the DB_FILES parameter.

Create a bigfile tablespace by using the CREATE BIGFILE TABLESPACE statement:

lotsadata DATAFILE '/oradata/db01/lotsadata.dbf' SIZE 10g;

You can also make bigfile tablespaces the default for your database, via the ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE statement.

Bigfile tablespaces can hold extraordinarily large amounts of data. Using the 8K block size, which is fairly common these days, you can store up to 32 terabytes in a single bigfile tablespace. Using the maximum block size of 32K along with the maximum possible number of datafiles (65,536), you can store up to 8 exabytes in a single database.

Note that you'll want to use bigfile tablespaces only in conjunction with a logical volume manager such as the new Oracle Database 10g Automatic Storage Management (ASM) feature.

If you're using Oracle ASM, you can create a bigfile tablespace that encompasses an entire disk group with a single datafile. To add space to such a tablespace, simply add another disk to the group and expand the size of the tablespace to reflect the addition:


You'll notice that there's no need here to even specify the datafile name. Because there's only one file, the system knows which one to expand.

It's important to use bigfile tablespaces only in conjunction with a volume manager such as Oracle ASM. Because there's only one file, you need a volume manager to stripe that file over multiple disks to distribute I/O. In addition, the only way to expand a bigfile tablespace is to expand the underlying datafile. Without a volume manager, that datafile's size is constrained to the size of the one disk drive on which the datafile resides.

Cross-Platform Transportable Tablespaces

Transportable tablespaces make it possible to efficiently move data between databases. Before Oracle8i, to move data from, say, an operational database to a data warehouse, you had to do something along the following lines:

  1. Perform an export, reading the data to be transferred from the source database datafiles and writing it to one or more disk files.

  2. Copy those disk files to your target system.

  3. Perform an import, reading the data from the export files and writing it to datafiles of the target database.

Although simple enough conceptually, this process involves a tremendous amount of I/O. The data to be transferred gets written three times: once to create the export files, once when those files are copied, and again when those files are imported. Similarly, the data gets read three times as well.

Transportable tablespaces add a bit of complexity to the process, but they vastly reduce the I/O, because data needs to be read and written only one time:

  1. Make the tablespaces to be transferred read-only.

  2. Check to ensure that they have no dependencies on objects in other tablespaces that aren't to be transferred.

  3. Copy the underlying datafiles to the target system.

  4. Import the associated metadata from the source database, possibly over a database link.

Arup Nanda shows this process very well in his Oracle Magazine September/October 2004 article "Moving Data Faster". Nanda also highlights Oracle Database 10g's ability to transfer datafiles between different vendors' platforms. Not only can you now transport tablespaces between, say, Microsoft Windows and Linux, but you can also now go between platforms that use different byte orders (that is, different endianness). Using the new CONVERT DATAFILE command of the Oracle Recovery Manager (RMAN), you can now change the byte order of a datafile to match that of your target platform.

Datafile Movement

If you use transportable tablespaces, you might be happy to learn about the new Oracle Database 10g DBMS_FILE_TRANSFER package. Using this package, you can copy the datafiles that make up a tablespace between databases from within a PL/SQL stored procedure. You can do everything else involving transportable tablespaces from PL/SQL as well, including using DBMS_DATAPUMP to import the metadata, so the end result is that you can schedule and execute transportable tablespace movements from within the database.

To move a datafile between databases, create a directory object, and grant appropriate permissions on the source database, use the following:

AS '/oradata/db01';
data_files TO system;
dbms_file_transfer TO system;

Similarly, take the same actions on the target system:

AS '/oradata/db02';
dbms_file_transfer TO system;

Next, create a database link, which in the following example is from the target back to the source system:

USING 'db01';

Finally, on the source system, put the tablespaces you are transporting into read-only mode:


With these preliminaries out of the way, you can execute the PL/SQL code in Listing 1 on the target system to pull the datafile(s) from the source system for the tablespace you want to move.

Code Listing 1: Pulling datafiles from the source

    job_handle NUMBER;
    job_handle := 

Note that for simplicity, this example uses a tablespace consisting of a single datafile. If a tablespace to be moved consists of several datafiles, you can move (or, rather, copy) them, simply by making successive calls to DBMS_FILE_COPY.

Tablespace Renaming

The new ability to rename tablespaces in Oracle Database 10g is a handy little feature for keeping things tidy. Have you ever wanted to rename a tablespace named, say, temp , to temp01 ? In Oracle Database 10g, the fix is as simple as

RENAME TO temp01;


The tablespace enhancements in Oracle Database 10g are a continuation of a long-running effort to simplify the task of database administration. If you're not taking full advantage of key enhancements such as locally managed tablespaces, default temporary tablespaces, and temporary tablespace groups, you're probably working harder than you need to.

Next Steps

 more about tablespaces
 "New Features for OCPs"


Photography byTim Arterbury,Unsplash