Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Tablespace Administration

Oracle Tips by Burleson

Carrying through with the analogy that Oracle is an operating system, we can say that tablespaces take the place of disks. But with this “disk,” you, the DBA, can specify its size and how it will create and store data (via the DEFAULT STORAGE clause) in its files (tables).

Tablespace Creation

Let’s look at the command for creating a tablespace.

               [MINIMUM EXTENT n [K|M]]
                    ON NEXT n K|M MAXSIZE UNLIMITED|n [K|M]]
               [DEFAULT (storage_clause)] (N/A If TEMPORARY above)
               [PERMANENT|TEMPORARY] (N/A If TEMPORARY above)
               [EXTENT MANAGEMENT
                             DICTIONARY (Must be LOCAL for TEMPFILE)
                                 UNIFORM  [SIZE n [K|M]]]

Oracle creates a system-managed UNDO (rollback, for us old-timers) tablespace (this feature is new with this version). If a database is created in UNDO automatic management mode, and no UNDO tablespace is specified in the CREATE DATABASE command, the SYSTEM tablespace will be used. AN UNDO tablespace uses AUTOALLOCATE LOCAL extent management. The database manages an UNDO tablespace, and no other objects can be assigned to it. You can only include the DATAFILE and EXTENT MANAGMEMENT LOCAL clauses for an UNDO tablespace. All UNDO tablespaces are permanent, read/write, and are in logging mode; and the values for MINIMUM EXTENT and DEFAULT STORAGE are system-generated.

tablespace. The name of the tablespace to be created.

DATAFILE. Specifies the datafile or files to comprise the tablespace.

TEMPFILE. IF tablespace is TEMPORARY, must use TEMPFILE; specifies the tempfiles to be used in the TEMPORARY tablespace.

MINIMUM EXTENT integer. Controls freespace fragmentation in the tablespace by ensuring that every in-use and/or free extent size in a tablespace is at least as large as, and is a multiple of, integer.

AUTOEXTEND. Enables or disables the automatic extension of datafile.

OFF. Disables AUTOEXTEND if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in later ALTER TABLESPACE AUTOEXTEND commands if OFF is specified; they are not persistent values.


NEXT. Disk space to allocate to the datafile when more extents are required.

MAXSIZE. Maximum disk space allowed for allocation to the datafile.

UNLIMITED. Set no limit on allocating disk space to the datafile.

LOGGING, NOLOGGING. Specifies the default logging attributes of all tables, index, and partitions within the tablespace. LOGGING is the default. If NOLOGGING is specified, no undo and redo logs are generated for operations that support the NOLOGGING option on the tables, index, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.

DEFAULT. Specifies the default storage parameters for all objects created in the tablespace.

ONLINE. Makes the tablespace available immediately after creation to users who have been granted access to the tablespace.

OFFLINE. Makes the tablespace unavailable immediately after creation. If you omit both the ONLINE and OFFLINE options, Oracle creates the tablespace online by default. The data dictionary view DBA_TABLESPACES indicates whether each tablespace is online or offline.

PERMANENT. Specifies that the tablespace will be used to hold permanent objects. This is the default.

TEMPORARY. Specifies that the tablespace will only be used to hold temporary objects; for example, segments used by implicit sorts to handle ORDER BY clauses.

EXTENT MANAGEMENT.  The EXTENT MANAGEMENT clause specifies how the extents in the tablespace will be managed. The default extent management is DICTIONARY, which is the standard method used by Oracle7 and Oracle8.0. The LOCAL option has space set aside for a bitmap.  LOCAL-managed extents are managed by the tablespace itself, while DICTIONARY-managed extents are managed by the data dictionary processes. The bitmap in a locally managed tablespace is used to track free and used extents. LOCAL management reduces recursive space management caused by rollback, data dictionary, and extent management because LOCAL management requires no dictionary actions and less rollback activity due to data dictionary extensions. LOCAL also allows for automatic mapping of adjacent free extents into a single large extent, eliminating the need for coalescing of freespace. The UNIFORM or AUTOALLOCATE determines how extents are mapped. AUTOALLOCATE, which forces system management of extents, uses the storage clause values for extent management, while UNIFORM uses a default of 1-MB extents. The DEFAULT STORAGE clause is invalid for locally managed tablespaces and cannot be specified; in addition, the MINIMUM EXTENT and TEMPORARY clauses are also prohibited if local extent management is utilized.

SEGMENT SPACE MANAGEMENT MANUAL|AUTO. New in 9i, this clause allows permanent, locally managed tablespaces to specify whether Oracle should track the used and freespace in segments using freelists or by use of bitmaps. MANUAL manages the freespace using freelists; AUTO manages the freespace using a bitmap. If AUTO is specified, any specification for FREELISTS or FREELIST GROUPS is ignored for objects stored in the tablespace. You will see this AUTO setting referred to as automatic segment-space management. It can be determined for a tablespace by use of the SEGMENT_SPACE_MANAGEMENT column in the DBA_ or USER_TABLESPACES view. Under AUTO, each LOCAL UNIFORM extent must be at least five Oracle blocks in size. If you specify LOCAL ALLOCATE, and the default database blocksize is 16K or greater, extents sizes will automatically be set to 1 megabyte at a minimum. The SYSTEM tablespace cannot be an AUTO type tablespace. LOBs cannot be stored in an AUTO type tablespace.

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. Let’s look at the actual command to create a tablespace with an initial 500-MB datafile with autoextension to a maximum size of 1 gigabyte (1,024 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 (they wouldn’t do that, would they?) let’s size the default storage to INITIAL 10M NEXT 1M PCTINCREASE 10.


I’ve included the PERMANENT, ONLINE, and LOGGING clauses for illustration only; they are the default if nothing is specified. Why did I specify a PCTINCREASE value? If PCTINCREASE is set to zero, the SMON process will not coalesce freespace. If PCTINCREASE is not specified, it will default to 50 percent; therefore, specifying it at a low value is suggested.

On the other hand, what if we wanted the extents to be locally managed due to the level of dynamic allocation that could happen? The CREATE TABLESPACE clause will change, in that we no longer specify the DEFAULT STORAGE clause, and instead use the EXTENT MANAGEMENT clause with the LOCAL clause. If we want to ensure that uniform extents are generated, we can specify the UNIFORM clause as well. Let’s shoot for 2-MB LOCAL UNIFORM extent management.


Now there are two types of temporary tablespaces: a CREATE TEMPORARY TABLESPACE tablespace, which uses TEMPFILES instead of DATAFILES, and a CREATE TABLESPACE tablespace TEMPORARY, which uses DATAFILES. A CREATE TEMPORARY TABLESPACE tablespace has to use LOCAL extent management. A CREATE TABLESPACE tablespace TEMPORARY cannot use LOCAL extent management. TEMPFILEs cannot be backed up using a hot backup; in fact, they don't have to be, and will generate an error if you attempt to place them in backup mode. Let's look at a couple of examples.

First let's create a CREATE TEMPORARY TABLESPACE tablespace:

TEMPFILE '/oracle1/oradata/ortest1/data/local_temp01.tmp' SIZE 500M

CREATE TEMPORARY TABLESPACE tablespaces (CT3s) will not release sort segments until the database is shut down. In tests on, this resulted in significant sort area overhead for a busy system. For a 6-gigabyte total size database with 40 concurrent users, 9 doing relatively heavy sorts, more than 16 gigabytes of temporary space was required. This appears to indicate the reuse mechanism isn't quite ready for prime time in CT3s.

Now let's create the equivalent CREATE TABLESPACE tablespace TEMPORARY tablepace:


CREATE TABLESPACE tablespace TEMPORARY (CT2) releases segments as soon as sorts end. For the same user and sort loads, an equivalent CT2 required 7 gigabytes of space compared to the 16 gigabytes required by the CT3. This reduced space requirement was due to the rapid release of the sort segments and reacquisition by processes requiring sorts. 

Sizing of Tablespaces

Tablespaces should be sized to contain all of the objects they are intended to hold. This means that, in order to size a tablespace properly, you will first need to size all of the objects that will be placed in the tablespace. Chapters 4 through 8, on table, cluster, and index management detail the techniques for sizing database objects.

The autoextend capability provided since Oracle7 can make some tablespace management easier, but it is no replacement for properly sizing tablespaces. One problem with autoextend is that you have no way of knowing when you will run out of space due to a runaway process (for example, a Cartesian product of 2 million row tables filling the temporary tablespace).

Once you have the sizes of all the objects that will reside in a tablespace, you add these size figures together; I suggest adding 25 to 50 percent additional room to allow for growth. The default storage parameters for a tablespace should never be used, except where the tablespace’s purpose is to hold rollback segments.

If a tablespace is built using RAW partitions on either UNIX or NT, I suggest selecting a standard size for the raw partitions, such as 501 MB or 1 GB (plus 1 MB), and create all raw segments this size.

Note: I suggest that the raw file sizes be 101, 501, and so on, to prevent writing data into a “zero” block, which can cause problems. Thus, a 100-megabyte datafile is mapped into a 101-megabyte RAW area. This practice may result in small amounts of wasted space, but it helps prevent some RAW problems from happening.

In order to minimize fragmentation issues (Swiss cheese-type fragmentation), it is suggested that several tablespaces (usually three), sized to hold various uniform extent sizes (small, medium, and large, for example), be created; objects that fit into each size model are placed into the appropriate tablespace. This would indicate that you would have three data and three index tablespaces to allow for the various tables and indexes in your application. The new UNIFORM or AUTOALLOCATE tablespace options lend themselves readily to this use of sizing models. 

In Oracle, the concept of Oracle-managed files (OMF) is introduced. Essentially, this model forces you to place all of your tablespace datafiles in the same directory. If you use OMF, then you must have a RAID5, a RAID0/1, or a RAID1/0 configuration. Basically, you set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_X initialization parameters; then Oracle will automatically create the datafiles, controlfiles, and log files in that location. You do not have to specify the tablespace datafile specification during CREATE or ALTER commands using OMF. If a filename is not specified, any file created is placed under the OMF control.

OMF should be used only for low-end or test databases, not for high-performance databases. OMF also should not be used with RAW disks.

Alteration of Tablespaces

Periodically, a tablespace may need to have its default storage changed; or require the addition of datafiles to increase its storage volume, a name change, or being taken offline for maintenance; or it may need to have AUTOEXTEND turned off or on; be made temporary, or converted to permanent; or require being placed in backup status for a hot backup. The command used for all of these functions is the ALTER command. Let’s look at its format.


       [ADD DATAFILE|TEMPFILE file_spec [autoextendclause]]
       [RENAME DATAFILE 'from_file_spec' TO 'to_file_spec']
       [DEFAULT STORAGE storage_clause]


See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter