Oracle8i Administrator's Guide
Release 2 (8.1.6)






Prev Next

Managing Tablespaces

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

Guidelines for Managing Tablespaces

Before working with tablespaces of an Oracle database, familiarize yourself with the guidelines provided in the following sections:

Use 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 factors and decide how many tablespaces you will need for your database design.

Specify 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. 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. You can also alter your default storage parameters at a later time.


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

Storage parameters are discussed in more detail in "Managing Tablespace Allocation".

Assign Tablespace Quotas to Users

Grant to 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.

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

Creating Tablespaces

Before you can create a tablespace you must create a database to contain it. The first tablespace in any database is always the SYSTEM tablespace, and the first datafiles of any database are automatically allocated in the SYSTEM tablespace during database creation. Creating a database was discussed in Chapter 2.

The steps for creating tablespaces vary by operating system. In all cases, however, you should create through your operating system a directory structure in which your datafiles will be allocated. 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.


No data can be inserted into any tablespace until the current instance has acquired at least two rollback segments (including the SYSTEM rollback segment). Rollback segments are discussed in Chapter 11, "Managing Rollback Segments"

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege.

Prior to Oracle8i, all tablespaces were created as dictionary-managed. Dictionary-managed tablespaces rely on SQL dictionary tables to track space utilization. Beginning with Oracle8i, you can now create locally managed tablespaces, which use bitmaps (instead of SQL dictionary tables) to track used and free space. For compatibility with earlier releases, dictionary-managed has been preserved as the default type of tablespace, but Oracle recommends that you now use locally managed tablespaces.

You can also create temporary tablespaces, which can be either dictionary-managed or locally managed. Each type of tablespace is discussed separately in the following sections:

Dictionary-Managed Tablespaces

For backwards compatibility, dictionary-managed remains the default method of space management in a tablespace. Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse.

Creating a Dictionary-Managed Tablespace

As an example, let's create the tablespace TBSA, with the following characteristics:

The following statement creates the tablespace TBSA:

    DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M
        INITIAL 50K
        NEXT 50K
        MINEXTENTS 2
        MAXEXTENTS 50
        PCTINCREASE 0);


If you do not fully specify the filename for a datafile, Oracle creates the datafile in the default database directory or the current directory, depending upon your operating system. Oracle recommends you always specify a fully qualified name. 

Altering a Dictionary-Managed Tablespace

Reasons for issuing an ALTER TABLESPACE statement include, but are not limited to:

Still other situations for altering a tablespace may be found elsewhere in this book.

Locally Managed Tablespaces

Locally managed tablespaces track all extent information in the tablespace itself, using bitmaps, resulting in the following benefits:

Additionally, the DBMS_SPACE_ADMIN package, discussed in "Using the DBMS_SPACE_ADMIN Package", provides maintenance procedures for locally managed tablespaces.

Creating a Locally Managed Tablespace

To create a locally managed tablespace, you specify LOCAL in the extent management clause of the CREATE TABLESPACE statement. You then have two options. You can have Oracle manage extents for you automatically with the AUTOALLOCATE option, or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM SIZE).

If the tablespace is expected to contain objects of varying sizes requiring different extent sizes and having many extents, then AUTOALLOCATE is the best choice. If it is not important to you to have a lot of control over space allocation and deallocation, AUTOALLOCATE presents a simplified way for you to manage a tablespace. Some space may be wasted but the benefit of having Oracle manage your space most likely outweighs this.

On the other hand, if you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. It ensures that you will never have an unusable amount of space in your tablespace.

The following statement creates a locally managed tablespace named LMTBSB, where AUTOALLOCATE causes Oracle to automatically manage extent size.

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M

Alternatively, this tablespace could be created specifying the UNIFORM clause. In this example, a 128K extent size is specified. Each 128K extent (which is equivalent to 64 Oracle blocks) is represented by a bit in the bitmap for this file.

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M

You cannot create a locally managed system tablespace.


When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if you do not specify the SIZE parameter in the extent management clause when UNIFORM is specified, the default extent size is 1MB. Therefore, in this case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB. 

Altering a Locally Managed Tablespace

You can alter a locally managed tablespace for many of the same reasons as a dictionary-managed tablespace. However, altering storage parameters is not an option and coalescing free extents is unnecessary for locally managed tablespaces. You also cannot alter a locally managed tablespace to a locally managed temporary tablespace.

Temporary Tablespaces

If you wish to improve the concurrence 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 for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.

You can view the allocation and deallocation of space in a temporary tablespace sort segment via the V$SORT_SEGMENT view, and the V$SORT_USAGE view identifies the current sort users in those segments.

You cannot explicitly create objects in a temporary tablespace. Assigning temporary tablespace to users is discussed in Chapter 22, "Managing Users and Resources".

See Also:

For more information about the V$SORT_SEGMENT and V$SORT_USAGE views, see the Oracle8i Reference.

A discussion on tuning sorts is contained in Oracle8i Designing and Tuning for Performance. 

Creating a Dictionary-Managed Temporary Tablespace

To identify a tablespace as temporary during tablespace creation, specify the TEMPORARY keyword on the CREATE TABLESPACE statement. The following statement creates a temporary dictionary-managed tablespace.

     DATAFILE '/u02/oracle/data/sort01.dbf' SIZE 50M
        INITIAL 2M
        NEXT 2M
        MINEXTENTS 1
        PCTINCREASE 0)

To change an existing permanent dictionary-managed tablespace to a temporary tablespace, use the ALTER TABLESPACE statement. For example:


You may issue the ALTER TABLESPACE statement against a dictionary-managed temporary tablespace using many of the same keywords and clauses as for a permanent dictionary-managed tablespace. Any restrictions are noted in the Oracle8i SQL Reference.


You can take dictionary-managed temporary tablespaces offline. Returning them online does not affect their temporary status. 

Creating a Locally Managed Temporary Tablespace

Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Therefore, they can be used in standby or read-only databases.

You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views. See "Viewing Information About Tablespaces" for a summary of views relating to tablespaces.

To create a locally managed temporary tablespace, you use the CREATE TEMPORARY TABLESPACE statement, which requires that you have the CREATE TABLESPACE system privilege.

The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks) is represented by a bit in the bitmap for the file.

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' 

Except for adding a tempfile, as illustrated in the following example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace.

   ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 2M REUSE;


You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace. 

However, the ALTER DATABASE statement can be used to alter tempfiles.

The following statements take offline and bring online temporary files:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

The following statement resizes temporary file u02/oracle/data/lmtemp02.dbf to 4M:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 4M;

The following statement drops a temporary file:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP;

It is also possible, but not shown, to AUTOEXTEND a tempfile and to rename (RENAME FILE) a tempfile.

Managing Tablespace Allocation

When you create a tablespace, you determine what physical datafile(s) comprise the tablespace and, for dictionary-managed tablespaces, what the default storage characteristics for the tablespace will be. Both of these attributes of the tablespace can be changed later. The default storage characteristics of a tablespace are discussed here; managing datafiles is the subject of Chapter 10, "Managing Datafiles".

Over time, the free space in a dictionary-managed tablespace can become fragmented, making it difficult to allocate new extents. Ways of defragmenting this free space are also discussed below.

These sections follow:

Storage Parameters in Locally Managed Tablespaces

When you allocate a locally managed tablespace, you cannot specify default storage parameters or minimum extent size. If AUTOALLOCATE is specified, the tablespace is system managed with the smallest extent size being 64K. If UNIFORM SIZE is specified, then the tablespace is managed with uniform size extents of the specified SIZE. The default SIZE is 1M.

When you allocate segments in a locally managed tablespace, the storage clause is interpreted differently than for dictionary-managed tablespaces. When an object is created in a locally managed tablespace, Oracle uses its INITIAL, NEXT, and MINEXTENTS parameters to calculate the initial size of the object's segment.

Storage Parameters for Dictionary-Managed Tablespaces

Storage parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used.

See Also:

For a discussion of the effects of these parameters, see Oracle8i Designing and Tuning for Performance.

For a complete description of storage parameters, see the Oracle8i SQL Reference. 

Specifying Default Storage Parameters

The following parameters influence segment storage allocation in a tablespace. They are referred to as storage parameters, and are contained in the storage_clause of the CREATE TABLESPACE statement.


Defines the size in bytes (K or M) of the first extent in the segment.  


Defines the size of the second extent in bytes. (K or M) 


The percent by which each extent, after the second (NEXT) extent, grows. 


The number of extents allocated when a segment is first created in the tablespace. 


Determines the maximum number of extents that a segment can have. Can also be specified as UNLIMITED. 

Another parameter on the CREATE TABLESPACE statement, MIMIMUM EXTENT, also influences segment allocation. If specified, it ensures that all free and allocated extents in the tablespace are at least as large as, and a multiple of, a specified number of bytes (K or M). This provides one means of controlling free space fragmentation in the tablespace.

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 the SQL statement ALTER TABLESPACE.

       NEXT 100K
       MAXEXTENTS 20
       PCTINCREASE 0);

The INITIAL and MINEXTENTS keywords cannot be specified in an ALTER statement. New values for the default storage parameters of a tablespace affect only future extents allocated for the segments within the tablespace.

Coalescing Free Space in Dictionary-Managed Tablespaces

A free extent in a tablespace is comprised of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the free extent closest in size to the required extent is used. In some cases, when segments are dropped, their extents are deallocated and marked as free, but any adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.

This fragmentation is addressed in several ways.

  1. When attempting to allocate a new extent for a segment, Oracle will first try to find a free extent large enough for the new extent. If no large enough free extent is found, Oracle will then coalesce adjacent free extents in the tablespace and look again. This coalescing is always performed by Oracle whenever it cannot find a free extent into which the new extent will fit.

  2. The SMON background process periodically coalesces neighboring free extents. When the PCTINCREASE value for a tablespace in nonzero. If you set PCTINCREASE=0, no coalescing of free extents will occur. If you are concerned about the overhead of SMON's ongoing coalescing, an alternative is to set PCTINCREASE=0, and periodically coalesce free space as noted in (4).

  3. When a segment is dropped or truncated, a limited form of coalescing is performed if the PCTINCREASE value for the segment is not zero. This is done even if PCTINCREASE=0 for the tablespace containing the segment.

  4. You can use the ALTER TABLESPACE...COALESCE statement to manually coalesce any adjacent free extents.

The process of coalescing free space is illustrated in the following figure.

Coalescing free space is not necessary for locally managed tablespaces.

For detailed information on allocating extents and coalescing free space, see Oracle8i Concepts.

Manually Coalescing Free Space

If you find that fragmentation of space in a tablespace is high (contiguous space on your disk appears as non-contiguous), you can coalesce any free space using the ALTER TABLESPACE...COALESCE statement. You must have the ALTER TABLESPACE system privilege to coalesce tablespaces.

You might want to use this statement if PCTINCREASE=0, or you can use it to supplement SMON and extent allocation coalescing. Note that if all extents within the tablespace are of the same size, coalescing is not necessary. This would be the case if the default PCTINCREASE value for the tablespace were set to zero, all segments used the default storage parameters of the tablespace, and INITIAL=NEXT=MINIMUM EXTENT.

The following statement coalesces free space in the tablespace TABSP_4.


Like other options of the ALTER TABLESPACE statement, the COALESCE option is exclusive: when specified, it must be the only option.

This statement does not coalesce free extents that are separated by data extents. If you observe that there are many free extents located between data extents, you must reorganize the tablespace (for example, by exporting and importing its data) to create useful free space extents.

Monitoring Free Space

You can use the DBA_FREE_SPACE and DBA_FREE_SPACE_COALESCED views to monitor free space and to display statistics for coalescing activity. The following statement displays the free space in tablespace TABSP_4.

SELECT  block_id, bytes, blocks 
FROM  dba_free_space
WHERE tablespace_name = 'TABSP_4'
ORDER BY block_id;

---------- ---------- ---------- 
         2      16384          2          
         4      16384          2          
         6      81920         10          
        16      16384          2          
        27      16384          2          
        29      16384          2          
        31      16384          2          
        33      16384          2          
        35      16384          2          
        37      16384          2          
        39       8192          1          
        40       8192          1          
        41     196608         24          
13 rows selected.

This view shows that there is adjacent free space in TABSP_4 (e.g., blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLE statement shown previously, the results of this query would read:

---------- ---------- ---------- 
         2     131072         16          
        27     311296         38          
2 rows selected.

To display statistics about coalescing activity use the DBA_FREE_SPACE_COALESCED view. It is also useful in determining if you need to coalesce space. For more information about either view, see Oracle8i Reference.

Altering Tablespace Availability

You can take an online tablespace offline so that this portion of the database is temporarily unavailable for general use but the rest is open and available. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open.

To alter the availability of a tablespace, use the SQL statement ALTER TABLESPACE. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege to perform this action.

Taking Tablespaces Offline

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

When a tablespace is taken offline, Oracle takes all the associated files offline. The SYSTEM tablespace may never be taken offline.

You can specify any of the following options when taking a tablespace 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. 


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 requires recovery before you can bring it back online. 


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. 


Takes the production database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information, see Oracle8i Backup and Recovery Guide.  


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. 

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

The following example takes the USERS tablespace offline normally:


Before taking an online tablespace offline, consider the following:

Bringing Tablespaces Online

You can bring any tablespace in an Oracle database online whenever the database is open. A tablespace is normally online so that the data contained within it is available to database users.


If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL option of the ALTER TABLESPACE OFFLINE statement), 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:


Read-Only Tablespaces

Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database, but they also provide a means of completely protecting historical data so that no one can modify the data after the fact. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.

Read-only tablespaces can also be transported to other databases. See Transporting Tablespaces Between Databases for more information on that functionality.


Making a tablespace read-only cannot in itself be used to satisfy archiving or data publishing requirements, because the tablespace can only be brought online in the database in which it was created. However, you may meet such requirements by using the transportable tablespace feature. 

You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in the tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to utilize the new description until the tablespace is made read-write.

Since read-only tablespaces can never be updated, they can reside on CD-ROM or WORM (Write Once-Read Many) devices. See "Creating a Read-Only Tablespace on a WORM Device".

The following topics are discussed in this section:

Making a Tablespace Read-Only

All tablespaces are initially created as read-write. Use the READ ONLY keywords in the ALTER TABLESPACE statement to change a tablespace to read-only. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

Before you can make a tablespace read-only, the following conditions must be met.

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.

The following statement makes the FLIGHTS tablespace read-only:


You do not have to wait for transactions to complete before issuing the ALTER TABLESPACE...READ ONLY statement. When the statement is issued, the target tablespace goes into a transitional read-only mode in which no further DML statements are allowed, though existing transactions that modified the tablespace will be allowed to commit or rollback. Once this occurs, the tablespace is quiesced, with respect to active transactions.


This transitional read-only state only occurs if the value of the initialization parameter COMPATIBLE is 8.1.0 or greater. For parameter values less than 8.1.0, the ALTER TABLESPACE...READ ONLY statement fails if any active transactions exist.  

If you find it is taking a long time for the tablespace to quiesce, it is possible to identify the transactions which are preventing the read-only state from taking effect. The owners of these transactions can be notified and a decision can be made to terminate the transactions, if necessary. The following example illustrates how you might identify the blocking transactions.

After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary since no changes can be made to it.

See Also:

For information about recovering a database with read-only datafiles, see the Oracle8i Backup and Recovery Guide

Making a Read-Only Tablespace Writable

Use the READ WRITE keywords in the ALTER TABLESPACE SQL statement to change a tablespace to allow write operations. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

A prerequisite to making the tablespace read-write is that all of the datafiles in the tablespace, as well as the tablespace itself, must be online. Use the DATAFILE... ONLINE clause of the ALTER DATABASE statement to bring a datafile online. The V$DATAFILE view lists the current status of datafiles.

The following statement makes the FLIGHTS tablespace writable


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

Creating a Read-Only Tablespace on a WORM Device

Follow these steps to create a read-only tablespace on a CD-ROM or WORM (Write Once-Read Many) device.

  1. Create a writable tablespace on another device. Create the objects that belong in the tablespace and insert your data.

  2. Alter 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. Use ALTER TABLESPACE with the RENAME DATAFILE clause. Renaming the datafiles changes their names in the control file.

  6. Bring the tablespace back online.

Delaying the Opening of Datafiles in Read Only Tablespaces

When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage, you should consider setting the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE. This speeds certain operations, primarily opening the database, by causing datafiles in read-only tablespaces to be accessed for the first time only when an attempt is made to read data stored within them.

Setting READ_ONLY_OPEN_DELAYED=TRUE has the following side-effects:

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 the SQL statement 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 specify the INCLUDING CONTENTS option. Use the CASCADE CONSTRAINTS option to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace.

Using the DBMS_SPACE_ADMIN Package

The DBMS_SPACE_ADMIN package provides administrators with defect diagnosis and repair functionality for locally managed tablespaces. The DBMS_SPACE_ADMIN package contains the following procedures:

Procedure  Description 


Verifies the consistency of the extent map of the segment. 


Marks the segment corrupt or valid so that appropriate error recovery can be done. 


Drops a segment currently marked corrupt (without reclaiming space). 


Dumps the segment header and extent map(s) of a given segment.  


Verifies that the bitmaps and extent maps for the segments in the tablespace are in sync.  


Rebuilds the appropriate bitmap(s).  


Marks the appropriate DBA range (extent) as free or used in bitmap.  


Rebuilds quotas for given tablespace.  


Migrates a locally managed tablespace to dictionary-managed tablespace. 


Migrates a tablespace from dictionary-managed format to locally managed format. 


Relocates the bitmaps to the destination specified. 


Fixes the state of the segments in a tablespace in which migration was aborted. 

The following scenarios describe typical situations in which you can use the DBMS_SPACE_ADMIN package to diagnose and resolve problems.


Some of these procedures may result in lost and unrecoverable data if not used properly. You should work with Oracle Worldwide Support if you have doubts about these procedures. 

See Also:

For details about the DBMS_SPACE_ADMIN package and procedures, see the Oracle8i Supplied PL/SQL Packages Reference

Scenario 1

The TABLESPACE_VERIFY procedure discovers that a segment has allocated blocks that are marked "free" in the bitmap and vice versa. It also discovers overlap between segments.

In this scenario, perform the following tasks:

Scenario 2

You cannot drop a segment because the bitmap has segment blocks marked "free."

In this scenario, perform the following tasks:

Scenario 3

The TABLESPACE_VERIFY procedure has reported some overlapping. Some of the real data must be sacrificed based on previous internal errors.

After choosing the object to be sacrificed, say table T1, perform the following tasks:

Scenario 4

A set of bitmap blocks has media corruption.

In this scenario, perform the following tasks:

Scenario 5

You migrate a dictionary-managed tablespace to a locally managed tablespace. You use the TABLESPACE_MIGRATE_TO_LOCAL procedure.

Let us assume that the database block size is 2K, and the existing extent sizes in tablespace TBS_1 are 10, 50, and 10,000 blocks (used, used, and free). The MINIMUM EXTENT value is 20K (10 blocks). In this scenario, you allow the bitmap allocation unit to be chosen by the system, The value of 10 blocks is chosen, because it is the highest common denominator and does not exceed MINIMUM EXTENT.

The statement to convert TBS_1 to a locally managed tablespace is as follows:


If you choose to specify a allocation unit size, it must be a factor of the unit size calculated by the system, otherwise an error message is issued.

Transporting Tablespaces Between Databases

This section describes how to transport tablespaces between databases, and includes the following topics:

Introduction to Transportable Tablespaces


You must have the Oracle8i Enterprise Edition to generate a transportable tablespace set. However, you can use any edition of Oracle8i to plug a transportable tablespace set into an Oracle database. 

You can use the transportable tablespaces feature to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. Transporting tablespaces is particularly useful for:

Moving data via transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

See Also:

For more details about transportable tablespaces and their use in data marts and data warehousing, see Oracle8i Concepts

For information about using transportable tablespaces to perform media recovery, see the Oracle8i Backup and Recovery Guide.

For information about transportable tablespace compatibility issues (between different Oracle releases), see Oracle8i Migration. 


Be aware of the following limitations as you plan for transportable tablespace use:

Procedure for Transporting Tablespaces Between Databases

To move or copy a set of tablespaces you must perform the following steps:

  1. Pick a self-contained set of tablespaces.

  2. Generate a transportable tablespace set.

    A transportable tablespace set consists of datafiles for the set of tablespaces being transported and a file containing structural information for the set of tablespaces.

  3. Transport the tablespace set.

    Copy the datafiles and the export file to the target database. You can do this using any facility for copying flat files (for example, an O/S copying utility, ftp, or publishing on CDs)

  4. Plug in the tablespace.

    Invoke Import to plug the set of tablespaces into the target database.

These steps are detailed below.

Step 1: Pick a Self-Contained Set of Tablespaces

You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. For example, if there is an index in the set of tablespaces for a table that is outside of the set of tablespaces, then the set of tablespaces is not self-contained.

The tablespace set you wish to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you wish to transport a subset of a partition table, you must exchange the partitions into tables.

When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether or not a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers. Some examples of self contained tablespace violations follow:

To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure. You specify the list of tablespace names and indicate whether you wish to transport referential integrity constraints.

For example, suppose you want to determine whether tablespaces SALES_1 and SALES_2 are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE). You can issue the following statement:

EXECUTE dbms_tts.transport_set_check('sales_1,sales_2', TRUE);

After invoking this PL/SQL routine, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view will be empty. The following query shows a case where there are two violations: a foreign key constraint, DEPT_FK, across the tablespace set boundary, and a partitioned table, JIM.SALES, that is partially contained in the tablespace set.

SELECT * FROM transport_set_violations;

Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table
JIM.DEPT in tablespace OTHER
Partitioned table JIM.SALES is partially contained in the transportable set

Object references (such as REFs) across the tablespace set are not considered violations. REFs are not checked by the TRANSPORT_SET_CHECK routine. When a tablespace containing dangling REFs is plugged into a database, queries following that dangling REF indicate user error.

See Also:

For more information about REFs, see the Oracle8i Application Developer's Guide - Fundamentals.

For more information about the DBMS_TTS package, see Oracle8i Supplied PL/SQL Packages Reference. 

Step 2: Generate a Transportable Tablespace Set

After identifying the self-contained set of tablespaces you want to transport, generate a transportable tablespace set by performing the following tasks:

  1. Make all tablespaces in the set you are copying read-only.

  2. Invoke the Export utility and specify which tablespaces are in the transportable set, as follows:

       TRIGGERS=y/n CONSTRAINTS=y/n GRANTS=y/n FILE=expdat.dmp


    Although the Export utility is used, only data dictionary structural information is exported. Hence, this operation is even quicker for a large tablespace. 

    When prompted, connect as "sys AS sysdba."

    You must always specify TABLESPACES. The FILE parameter specifies the name of the structural information export file to be created.

    If you set TRIGGERS=n, triggers are not exported. If you set TRIGGERS=y, triggers are exported without a validity check. Invalid triggers cause compilation errors during the subsequent import.

    If you set GRANTS=y, all grants on the exported tables are exported too; otherwise, all GRANTS are ignored.

    If you set CONSTRAINTS=y, referential integrity constraints are exported; otherwise, referential integrity constraints are ignored.

    The default setting for all of these options is 'y.'

  3. Copy the datafiles to a separate storage space or to the target database.

  4. If necessary, put the tablespaces in the copied set back into read-write mode as follows:


If the tablespace sets being transported are not self-contained, export will fail and indicate that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.

See Also:

For information about using the Export utility, refer to Oracle8i Utilities. 

Step 3: Transport the Tablespace Set

Transport both the datafiles and the export file to a place accessible to the target database. You can use any facility for copying flat files (for example, an O/S copying utility, ftp, or publishing on CDs).

Step 4: Plug In the Tablespace Set

To plug in a tablespace set, perform the following tasks:

  1. Put the copied tablespace set datafiles in a location where the target database can access them.

  2. Plug in the tablespaces and integrate the structural information using the following import statement:

    IMP TRANSPORT_TABLESPACE=y DATAFILES=('/db/sales_jan','/db/sales_feb',...)
        TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee)  
        FROMUSER=(dcranney,jfee) TOUSER=(smith,williams) FILE=expdat.dmp

    When prompted, connect as "sys AS sysdba."

    Following are two more examples:

    IMP TRANSPORT_TABLESPACE=y DATAFILES=('/db/staging1.f','/db/staging2.f')

    You must specify DATAFILES.

    TABLESPACES,TTS_OWNERS, FROMUSER and TOUSER are optional. The FILE parameter specifies the name of the structural information export file.

    When you specify TABLESPACES, the supplied tablespace names are compared to those in the export file. Import returns an error if there is any mismatch. Otherwise, tablespace names are extracted from the export file.

    TTS_OWNERS lists all users who own data in the tablespace set. When you specify TTS_OWNERS, the user names are compared to those in the export file. Import returns an error if there is any mismatch. Otherwise, owner names are extracted from the export file.

    If you do not specify FROMUSER and TOUSER, all database objects (such as tables and indexes) will be created under the same user as in the source database. Those users must already exist in the target database. If not, import will return an error indicating that some required users do not exist in the target database.

    You can use FROMUSER and TOUSER to change the owners of objects. For example, if you specify FROMUSER=(dcranney,jfee) TOUSER=(smith, williams() objects in the tablespace set owned by DCRANNEY in the source database will be owned by SMITH in the target database after the tablespace set is plugged in. Similarly, objects owned by JFEE in the source database will be owned by WILLIAMS in the target database. In this case, the target database does not have to have users DCRANNEY and JFEE, but must have users SMITH and WILLIAMS.

    After this statement successfully executes, all tablespaces in the set being copied remain in read-only mode. You should check the import logs to ensure no error has occurred. At this point, you can issue the ALTER TABLESPACE...READ WRITE statement to place the new tablespaces in read-write mode.

    When dealing with a large number of datafiles, specifying the list of datafile names in the statement line can be a laborious process; it may even exceed the statement line limit. In this situation, you may use an import parameter file. For example, one of the statements in this step is equivalent to the following:

    IMP PARFILE='par.f'

    The file par.f contains the following:


    To transport a tablespace between databases, both the source and target database must be running Oracle8i, with the initialization file compatibility parameter set to 8.1.

    See Also:

    For information about using the Import utility, refer to Oracle8i Utilities. 

    Object Behaviors

    Most objects, whether data in a tablespace or structural information associated with the tablespace, behave normally after being transported to a different database. However, the following objects are exceptions:


    When a database contains tablespaces that have been plugged in (from other databases), the ROWIDs in that database are no longer unique. A ROWID is guaranteed unique only within a table.


    REFs are not checked when Oracle determines if a set of tablespaces is self-contained. As a result, a plugged-in tablespace may contain dangling REFs. Any query following dangling REFs returns a user error.


    Privileges are transported if you specify GRANTS=y during export. During import, some grants may fail. For example, the user being granted a certain right may not exist, or a role being granted a particular right may not exist.

    Partitioned Tables

    You cannot move a partitioned table via transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces. You must ensure that all partitions in a table are in the tablespace set, or exchange the partitions into tables before copying the tablespace set. However, you should note that exchanging partitions with tables invalidates the global index of the partitioned table.

    At the target database, you can exchange the tables back into partitions if there is already a partitioned table that exactly matches the column in the target database. If all partitions of that table come from the same foreign database, the exchange operation is guaranteed to succeed. If they do not, in rare cases, the exchange operation may return an error indicating that there is a data object number conflict.

    If you receive a data object number conflict error when exchanging tables back into partitions, you can move the offending partition using the ALTER TABLE MOVE PARTITION statement. After doing so, retry the exchange operation.

    If you specify the WITHOUT VALIDATION option of the exchange statement, the statement will return immediately because it only manipulates structural information. Moving partitions, however, may be slow because the data in the partition can be copied. See "Transporting and Attaching Partitions for Data Warehousing" for an example using partitioned tables.


    A transportable tablespace set can contain:

    • Tables

    • Indexes

    • Bitmap indexes

    • Index-organized tables

    • LOBs

    • Nested tables

    • Varrays

    • Tables with user-defined type columns

    If the tablespace set contains a pointer to a BFILE, you must move the BFILE and set the directory correctly in the target database.

    Advanced Queues

    You can use transportable tablespaces to move or copy Oracle advanced queues, as long as these queues are not 8.0-compatible queues with multiple recipients. After a queue is transported to a target database, the queue is initially disabled. After making the transported tablespaces read-write in the target database, you can enable the queue by starting it up via the built-in PL/SQL routine DBMS_AQADM.START_QUEUE.


    You can transport regular indexes and bitmap indexes. When the transportable set fully contains a partitioned table, you can also transport the global index of the partitioned table.

    Function-based indexes and domain indexes are not supported. If they exist in a tablespace, you must drop them before you can transport the tablespace.


    Triggers are exported without a validity check. In other words, Oracle does not verify that the trigger refers only to objects within the transportable set. Invalid triggers will cause a compilation error during the subsequent import.


    Transporting snapshot or replication structural information is not supported. If a table in the tablespace you want to transport is replicated, you must drop the replication structural information and convert the table into a normal table before you can transport the tablespace.

    Using Transportable Tablespaces

    The following are some possible applications for transportable tablespaces.

    Transporting and Attaching Partitions for Data Warehousing

    Typical enterprise data warehouses contain one or more large fact tables. These fact tables may be partitioned by date, making the enterprise data warehouse a historical database. You can build indexes to speed up star queries. In fact, Oracle recommends that you build local indexes for such historically partitioned tables to avoid rebuilding global indexes every time you drop the oldest partition from the historical database.

    Suppose every month you would like to load one month's worth of data into the data warehouse. There is a large fact table in the data warehouse called SALES, which has the following columns:

    CREATE TABLE sales (invoice_no NUMBER,
       sale_year  INT NOT NULL,
       sale_month INT NOT NULL,
       sale_day   INT NOT NULL)
       PARTITION BY RANGE (sale_year, sale_month, sale_day)
         (partition jan98 VALUES LESS THAN (1998, 2, 1),
          partition feb98 VALUES LESS THAN (1998, 3, 1),
          partition mar98 VALUES LESS THAN (1998, 4, 1),
          partition apr98 VALUES LESS THAN (1998, 5, 1),
          partition may98 VALUES LESS THAN (1998, 6, 1),
          partition jun98 VALUES LESS THAN (1998, 7, 1));

    You create a local nonprefixed index:

    CREATE INDEX sales_index ON sales(invoice_no) LOCAL;

    Initially, all partitions are empty, and are in the same default tablespace. Each month, you wish to create one partition and attach it to the partitioned SALES table.

    Suppose it is July 1998, and you would like to load the July sales data into the partitioned table. In a staging database, you create a new tablespace, TS_JUL. You also create a table, JUL_SALES, in that tablespace with exactly the same column types as the SALES table. You can create the table JUL_SALES using the CREATE TABLE...AS SELECT statement. After creating and populating JUL_SALES, you can also create an index, JUL_SALE_INDEX, for the table, indexing the same column as the local indexes in the SALES table. After building the index, transport the tablespace TS_JUL to the data warehouse.

    In the data warehouse, add a partition to the SALES table for the July sales data. This also creates another partition for the local nonprefixed index:

    ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1);

    Attach the transported table JUL_SALES to the table SALES by exchanging it with the new partition:


    This statement places the July sales data into the new partition JUL98, attaching the new data to the partitioned table. This statement also converts the index JUL_SALE_INDEX into a partition of the local index for the SALES table. This statement should return immediately, because it only operates on the structural information; it simply switches database pointers. If you know that the data in the new partition does not overlap with data in previous partitions, you are advised to specify the WITHOUT VALIDATION option; otherwise the statement will go through all the new data in the new partition in an attempt to validate the range of that partition.

    If all partitions of the SALES table came from the same staging database (the staging database is never destroyed), the exchange statement will always succeed. In general, however, if data in a partitioned table comes from different databases, it's possible that the exchange operation may fail. For example, if the JUL98 partition of SALES did not come from the same staging database, the above exchange operation can fail, returning the following error:

    ORA-19728: data object number conflict between table JUL_SALES and partition 
    JAN98 in table SALES

    To resolve this conflict, move the offending partition by issuing the following statement:


    Then retry the exchange operation.

    After the exchange succeeds, you can safely drop JUL_SALES and JUL_SALE_INDEX (both are now empty). Thus you have successfully loaded the July sales data into your data warehouse.

    Publishing Structured Data on CDs

    Transportable tablespaces provide a way to publish structured data on CDs. A data provider may load a tablespace with data to be published, generate the transportable set, and copy the transportable set to a CD. This CD can then be distributed.

    When customers receive this CD, they can plug it into an existing database without having to copy the datafiles from the CD to disk storage. For example, suppose on an NT machine D: drive is the CD drive. You can plug in a transportable set with datafile catalog.f and export file expdat.dmp as follows:

    IMP TRANSPORT_TABLESPACE=y DATAFILES='D:\catalog.f' FILE='D:\expdat.dmp'

    You can remove the CD while the database is still up. Subsequent queries to the tablespace will return an error indicating that Oracle cannot open the datafiles on the CD. However, operations to other parts of the database are not affected. Placing the CD back into the drive makes the tablespace readable again.

    Removing the CD is the same as removing the datafiles of a read-only tablespace. If you shut down and restart the database, Oracle will indicate that it cannot find the removed datafile and will not open the database (unless you set the initialization parameter READ_ONLY_OPEN_DELAYED to true). When READ_ONLY_OPEN_DELAYED is set to TRUE, Oracle reads the file only when someone queries the plugged-in tablespace. Thus, when plugging in a tablespace on a CD, you should always set the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE, unless the CD is permanently attached to the database.

    Mounting the Same Tablespace Read-only on Multiple Databases

    You can use transportable tablespaces to mount a tablespace read-only on multiple databases. In this way, separate databases can share the same data on disk instead of duplicating data on separate disks. The tablespace datafiles must be accessible by all databases. To avoid database corruption, the tablespace must remain read-only in all the databases mounting the tablespace.

    You can mount the same tablespace read-only on multiple databases in either of the following ways:

    • Plug the tablespaces into each of the databases you wish to mount the tablespace. Generate a transportable set in a single database. Put the datafiles in the transportable set on a disk accessible to all databases. Import the structural information into each database.

    • Generate the transportable set in one of the databases and plug it into other databases. If you use this approach, it is assumed that the datafiles are already on the shared disk, and they belong to an existing tablespace in one of the databases. You can make the tablespace read-only, generate the transportable set, and then plug the tablespace in to other databases while the datafiles remain in the same location on the shared disk.

    You can make the disk accessible by multiple computers via several ways. You may use either a clustered file system or raw disk, as that is required by Oracle Parallel Server. Because Oracle will only read these type of datafiles on shared disk, you can also use NFS. Be aware, however, that if a user queries the shared tablespace while NFS is down, the database may hang until the NFS operation times out.

    Later, you can drop the read-only tablespace in some of the databases. Doing so will not modify the datafiles for the tablespace; thus the drop operation will not corrupt the tablespace. Do not make the tablespace read-write unless only one database is mounting the tablespace.

    Archive Historical Data via Transportable Tablespaces

    Since a transportable tablespace set is a self-contained set of files that can be plugged into any Oracle database, you can archive old/historical data in an enterprise data warehouse via the transportable tablespace procedures described in this chapter.

    See Also:

    For more details, see the Oracle8i Backup and Recovery Guide. 

    Using Transportable Tablespaces to Perform TSPITR

    You can use transportable tablespaces to perform tablespace point-in-time recovery (TSPITR).

    See Also:

    For information about how to perform TSPITR using transportable tablespaces, see the Oracle8i Backup and Recovery Guide. 

    Viewing Information About Tablespaces

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

    View  Description 


    Name and number of all tablespaces from the controlfile. 


    Descriptions of all (or user accessible) tablespaces. 


    Information about segments within all (or user accessible) tablespaces. 


    Information about data extents within all (or user accessible) tablespaces. 


    Information about free extents within all (or user accessible) tablespaces. 


    Information about all datafiles, including tablespace number of owning tablespace. 


    Information about all tempfiles, including tablespace number of owning tablespace. 


    Shows files (datafiles) belonging to tablespaces. 


    Shows files (tempfiles) belonging to temporary tablespaces. 


    Information for all extents in all locally managed temporary tablespaces.  


    For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance. 


    Shows space used/free for each tempfile. 


    Default and temporary tablespaces for all users. 


    Lists tablespace quotas for all users.  


    Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.  


    Temporary sort space usage by user and temporary/permanent tablespace. 

    The following are just a few examples of using some of these views.

    See Also:

    A complete description of these views is contained in Oracle8i Reference. 

    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 dba_tablespaces;
    ----------  -----------  --------  -------   -------    ------------ 
    RBS             1048576   1048576        2        40               0
    SYSTEM           106496    106496        1        99               1
    TEMP             106496    106496        1        99               0
    TESTTBS           57344     16384        2        10               1
    USERS             57344     57344        1        99               1

    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, blocks, tablespace_name
       FROM dba_data_files;
    FILE_NAME                                      BLOCKS  TABLESPACE_NAME
    ------------                               ----------  -------------------
    /U02/ORACLE/IDDB3/RBS01.DBF                      1536  RBS
    /U02/ORACLE/IDDB3/SYSTEM01.DBF                   6586  SYSTEM
    /U02/ORACLE/IDDB3/TEMP01.DBF                     6400  TEMP
    /U02/ORACLE/IDDB3/TESTTBS01.DBF                  6400  TESTTBS
    /U02/ORACLE/IDDB3/USERS01.DBF                     384  USERS

    Statistics for Free Space (Extents) of Each Tablespace: Example

    To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

    SELECT tablespace_name "TABLESPACE", 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;
    ----------    -------  ------   -------    -------  -------   ------
    RBS                 2       1       955        955      955      955
    SYSTEM              1       1       119        119      119      119
    TEMP                4       1      6399       6399     6399     6399
    TESTTBS             5       5      6364          3     1278     6390
    USERS               3       1       363        363      363      363

    PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. 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.

Prev Next
Copyright © 1999 Oracle Corporation.

All Rights Reserved.