Oracle8 Administrator's Guide
Release 8.0






Prev Next

Managing Tablespaces

This chapter describes the various aspects of tablespace management, and includes the following topics:

This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Enterprise Manager Administrator's Guide and Oracle Enterprise Manager User's Guide.

Guidelines for Managing Tablespaces

Before working with tablespaces of an Oracle database, consider the guidelines in the following sections:

Using Multiple Tablespaces

Using multiple tablespaces allows you more flexibility in performing database operations. For example, when a database has multiple tablespaces, you can perform the following tasks:

Some operating systems set a limit on the number of files that can be simultaneously open; these limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system's limit, plan your tablespaces efficiently. Create only enough tablespaces to fill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with the autoextend option set on, rather than many small datafiles.

Review your data in light of these advantages and decide how many tablespaces you will need for your database design.

Specifying Tablespace Storage Parameters

When you create a new tablespace, you can specify default storage parameters for objects that will be created in the tablespace. Storage parameters specified when an object is created override the default storage parameters of the tablespace containing the object. However, if you do not specify storage parameters when creating an object, the object's segment automatically uses the default storage parameters for the tablespace.

Set the default storage parameters for a tablespace to account for the size of a typical object that the tablespace will contain (you estimate this size). You can specify different storage parameters for an unusual or exceptional object when creating that object.


If you do not specify the default storage parameters for a new tablespace, the default storage parameters of Oracle become the tablespace's default storage parameters.


See Also: For information about estimating the sizes of objects, see Chapters 9 through 16.

Assigning Tablespace Quotas to Users

Grant users who will be creating tables, clusters, snapshots, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object's segment. The security administrator is responsible for granting the required privileges to create objects to database users and for assigning tablespace quotas, as necessary, to database users.

See Also: To learn more about assigning tablespace quotas to database users, see "Assigning Tablespace Quotas".

Creating Tablespaces

The steps for creating tablespaces vary by operating system. On most operating systems you indicate the size and fully specified filenames when creating a new tablespace or altering a tablespace by adding datafiles. In each situation Oracle automatically allocates and formats the datafiles as specified. However, on some operating systems, you must create the datafiles before installation.

The first tablespace in any database is always the SYSTEM tablespace. Therefore, the first datafiles of any database are automatically allocated for the SYSTEM tablespace during database creation.

You might create a new tablespace for any of the following reasons:

To increase the total size of the database you can alternatively add a datafile to an existing tablespace, rather than adding a new tablespace.


No data can be inserted into any tablespace until the current instance has acquired at least two rollback segments (including the SYSTEM rollback segment).


To create a new tablespace, use either the Create Tablespace property sheet of Enterprise Manager/GUI, or the SQL command CREATE TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace.

As an example, let's create the tablespace RB_SEGS (to hold rollback segments for the database), with the following characteristics:

The following statement creates the tablespace RB_SEGS:

      DATAFILE 'datafilers_1' SIZE 50M
        INITIAL 50K
        NEXT 50K
        MINEXTENTS 2
        MAXEXTENTS 50
        PCTINCREASE 0)

If you do not fully specify filenames when creating tablespaces, the corresponding datafiles are created in the current directory of the database server.

See Also: See your operating system-specific Oracle documentation for information about initially creating a tablespace.

For more information about adding a datafile, see "Creating and Adding Datafiles to a Tablespace".

For more information about the CREATE TABLESPACE statement, see the Oracle8 Reference.

Creating a Temporary Tablespace

If you wish to improve the concurrency of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, you can create temporary tablespaces.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist in every instance that performs sort operations within a given tablespace. You cannot store permanent objects in a temporary tablespace. You can view the allocation and deallocation of space in a temporary tablespace sort segment via the V$SORT_SEGMENT table.

To identify a tablespace as temporary during tablespace creation, issue the following statement:


To identify a tablespace as temporary in an existing tablespace, issue the following statement:



You can take temporary tablespaces offline. Returning temporary tablespaces online does not affect their temporary status.


See Also: For more information about the CREATE TABLESPACE and ALTER TABLESPACE commands, see the Oracle8 SQL Reference.

For more information about V$SORT_SEGMENT, see the Oracle8 Reference.

For more information about Oracle space management, see Oracle8 Concepts.

Managing Tablespace Allocation

This section describes aspects of managing tablespace allocation, and includes the following topics:

Altering Storage Settings for Tablespaces

You can change the default storage parameters of a tablespace to change the default specifications for future objects created in the tablespace. To change the default storage parameters for objects subsequently created in the tablespace, use either the Alter Tablespace property sheet of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. Also, to alter the default storage parameters of a tablespace, you must have the ALTER TABLESPACE system privilege.

       INITIAL 50K
       NEXT 50K
       MAXEXTENTS 20
       PCTINCREASE 50);

New values for the default storage parameters of a tablespace affect only future extents allocated for the segments within the tablespace.

Coalescing Free Space

Space for tablespace segments is managed using extents, which are comprised of a specific number of contiguous data blocks. The free extent closest in size to the required extent is used when allocating new extents to a tablespace segment. Thus, a larger free extent can be fragmented, or smaller contiguous free extents can be coalesced into one larger free extent (see Figure 8-1). However, continuous allocation and deallocation of free space fragments your tablespace and makes allocation of larger extents more difficult. By default, SMON (system monitor) processes incrementally coalesce the free extents of tablespaces in the background. If desired, you can disable SMON coalescing.

Figure 8-1 Coalescing Free Space

If you find that fragmentation of space is high (contiguous space on your disk appears as non-contiguous), you can coalesce your free space in a single space transaction. After every eight coalesces the space transaction commits and other transactions can allocate or deallocate space. You must have ALTER TABLESPACE privileges to coalesce tablespaces. You can coalesce all available free space extents in a tablespace into larger contiguous extents on a per tablespace basis by using the following command:


You can also use this command to supplement SMON and extent allocation coalescing, thereby improving space allocation performance in severely fragmented tablespaces. Issuing this command does not effect the performance of other users accessing the same tablespace. Like other options of the ALTER TABLESPACE command, the COALESCE option is exclusive; when specified, it should be the only option.

Viewing Information about Tablespaces

To display statistics about coalesceable extents for tablespaces, you can view the DBA_FREE_SPACE_COALESCED view. You can query this view to determine if you need to coalesce space in a particular tablespace.

See Also: For information about the contents of DBA_FREE_SPACE_COALESCED, see the Oracle8 Reference.

Altering Tablespace Availability

You can bring an offline tablespace online to make the schema objects within the tablespace available to database users. Alternatively, you can take an online tablespace offline while the database is open, so that this portion of the database is temporarily unavailable for general use but the rest is open and available. This section includes the following topics:

Bringing Tablespaces Online

You can bring any tablespace in an Oracle database online whenever the database is open. The only exception is that the SYSTEM tablespace must always be online because the data dictionary must always be available to Oracle. A tablespace is normally online so that the data contained within it is available to database users.

To bring an offline tablespace online while the database is open, use either the Place Online menu item of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to bring a tablespace online.


If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL option of the ALTER TABLESPACE OFFLINE command), you must first perform media recovery on the tablespace before bringing it online. Otherwise, Oracle returns an error and the tablespace remains offline.


The following statement brings the USERS tablespace online:


Taking Tablespaces Offline

You may wish to take a tablespace offline for any of the following reasons:

To take an online tablespace offline while the database is open, use either the Take Offline menu item of Enterprise Manager/GUI, or the SQL command ALTER TABLESPACE. You must have the MANAGE TABLESPACE system privilege to take a tablespace offline.

You can specify any of the following priorities when taking a tablespace offline:

normal offline


A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error. With normal offline priority, Oracle takes a checkpoint for all datafiles of the tablespace as it takes them offline.


temporary offline


A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. With temporary offline priority, Oracle takes offline the datafiles that are not already offline, checkpointing them as it does so.



If no files are offline, but you use the temporary option, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace will require recovery before you can bring it back online.


immediate offline


A tablespace can be taken offline immediately, without Oracle's taking a checkpoint on any of the datafiles. With immediate offline priority, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode.



If you must take a tablespace offline, use the normal option (the default) if possible; this guarantees that the tablespace will not require recovery to come back online, even if you reset the redo log sequence (using an ALTER DATABASE OPEN RESETLOGS statement after incomplete media recovery) before bringing the tablespace back online.


Take a tablespace offline temporarily only when you cannot take it offline normally; in this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Take a tablespace offline immediately only after trying both the normal and temporary options.

The following example takes the USERS tablespace offline normally:


See Also: Before taking an online tablespace offline, verify that the tablespace contains no active rollback segments. For more information see "Taking Rollback Segments Offline".

Making a Tablespace Read-Only

This section describes issues related to making tablespaces read-only, and includes the following topics:

Making a tablespace read-only prevents further write operations on the datafiles in the tablespace. After making the tablespace read-only, you should back it up.

Use the SQL command ALTER TABLESPACE to change a tablespace to read-only. You must have the ALTER TABLESPACE system privilege to make a tablespace read-only. The following statement makes the FLIGHTS tablespace read-only:


After a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL command ALTER DATABASE RENAME.

A read-only tablespace is neither online nor offline. Issuing the ALTER TABLESPACE command with the ONLINE or OFFLINE option does not change the read-only state of the tablespace; rather, it causes all of the datafiles in the tablespace to be brought online or offline.


Before you can make a tablespace read-only, the following conditions must be met. It may be easiest to meet these restrictions by performing this function in restricted mode, so that only users with the RESTRICTED SESSION system privilege can be logged on.

For better performance while accessing data in a read-only tablespace, you might want to issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table will ensure that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for Oracle to check the status of the transactions that most recently modified the blocks.


You cannot rename or resize datafiles belonging to a read-only tablespace.


See Also: For more information about read-only tablespaces, see Oracle8 Concepts.

Making a Read-Only Tablespace Writeable

Whenever you create a tablespace, it is both readable and writeable. To change a read-only tablespace back to a read-write tablespace, use the SQL command ALTER TABLESPACE. You must have the ALTER TABLESPACE system privilege to change a read-only tablespace to a read-write tablespace. The following command makes the FLIGHTS tablespace writeable:


Making a read-only tablespace writeable updates the control file for the datafiles, so that you can use the read-only version of the datafiles as a starting point for recovery.


To issue this command, all of the datafiles in the tablespace must be online. Use the DATAFILE ONLINE option of the ALTER DATABASE command to bring a datafile online. The V$DATAFILE view lists the current status of a datafile.

Creating a Read-Only Tablespace on a WORM Device

To create a read-only tablespace on a WORM (Write Once Read Many) device when you have read-only files that do not require updating:

  1. Create a writeable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
  2. Issue the ALTER TABLESPACE command with the READ ONLY option to change the tablespace to read-only.
  3. Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
  4. Take the tablespace offline.
  5. Rename the datafiles to coincide with the names of the datafiles you copied onto your WORM device. Renaming the datafiles changes their names in the control file.
  6. Bring the tablespace online.

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. Any tablespace in an Oracle database, except the SYSTEM tablespace, can be dropped. You must have the DROP TABLESPACE system privilege to drop a tablespace.


Once a tablespace has been dropped, the tablespace's data is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely. This is strongly recommended so that you can recover the database if you mistakenly drop a tablespace, or if the database experiences a problem in the future after the tablespace has been dropped.


When you drop a tablespace, only the file pointers in the control files of the associated database are dropped. The datafiles that constituted the dropped tablespace continue to exist. To free previously used disk space, delete the datafiles of the dropped tablespace using the appropriate commands of your operating system after completing this procedure.

You cannot drop a tablespace that contains any active segments. For example, if a table in the tablespace is currently being used or the tablespace contains an active rollback segment, you cannot drop the tablespace. For simplicity, take the tablespace offline before dropping it.

After a tablespace is dropped, the tablespace's entry remains in the data dictionary (see the DBA_TABLESPACES view), but the tablespace's status is changed to INVALID.

To drop a tablespace, use either the Drop tablespace menu item of Enterprise Manager/GUI, or the SQL command DROP TABLESPACE. The following statement drops the USERS tablespace, including the segments in the tablespace:


If the tablespace is empty (does not contain any tables, views, or other structures), you do not need to check the Including Contained Objects checkbox. If the tablespace contains any tables with primary or unique keys referenced by foreign keys of tables in other tablespaces and you want to cascade the drop of the FOREIGN KEY constraints of the child tables, select the Cascade Drop of Integrity Constraints checkbox to drop the tablespace.

Use the CASCADE CONSTRAINTS option to cascade the drop of the FOREIGN KEY constraints in the child tables.

See Also: For more information about taking tablespaces offline, see "Taking Tablespaces Offline".

For more information about the DROP TABLESPACE statement, see the Oracle8 SQL Reference.

Viewing Information About Tablespaces

The following data dictionary views provide useful information about tablespaces of a database:

The following examples illustrate how to use the views not already illustrated in other chapters of this manual. They assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally.

Listing Tablespaces and Default Storage Parameters: Example

To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:

SELECT tablespace_name "TABLESPACE",
   initial_extent "INITIAL_EXT",
   next_extent "NEXT_EXT",
   min_extents "MIN_EXT",
   max_extents "MAX_EXT",
   FROM sys.dba_tablespaces;

----------  -----------  --------  -------   -------    ------------ 
SYSTEM      10240000     10240000        1      99            50
USERS       10240000     10240000        1      99            50

Listing the Datafiles and Associated Tablespaces of a Database: Example

To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

SELECT  file_name, bytes, tablespace_name
   FROM sys.dba_data_files;

------------ ---------- -------------------
filename1    10240000   SYSTEM
filename2    10240000   USERS
filename3    20480000   USERS

Listing the Free Space (Extents) of Each Tablespace: Example

To see the amount of space available in the free extents of each tablespace in the database, enter the following query:

SELECT tablespace_name, file_id,
   COUNT(*)    "PIECES",
   MAX(blocks) "MAXIMUM",
   MIN(blocks) "MINIMUM",
   AVG(blocks) "AVERAGE",
   SUM(blocks) "TOTAL"
   FROM sys.dba_free_space
WHERE tablespace_name = 'SYSTEM'
GROUP BY tablespace_name, file_id;

----------    -------  ------   -------    -------  -------   ------
SYSTEM             1       2      2928        115   1521.5    3043

SUM shows the amount of free space in each tablespace, PIECES shows the amount of fragmentation in the datafiles of the tablespace, and MAXIMUM shows the largest contiguous area of space. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.


Copyright © 1997 Oracle Corporation.

All Rights Reserved.