In this Document

Applies to:

Purpose

This document is intended to assist Database Administrators resolve issues encountered involving managing tablespaces.

Troubleshooting Steps

Concepts

A database is divided into logical storage units called tablespaces, which group related logical structures (such as tables, views, and other database objects). A tablespace consists of one or more physical data files. Database objects assigned to a tablespace are stored in the physical data files of that tablespace. When you create an Oracle database, some tablespaces already exist, such as SYSTEM and SYSAUX(as of 10g).

Tablespaces provide a means to physically locate data on storage. When you define the data files that comprise a tablespace, you specify a storage location for these files. For example, you might specify a data file location for a certain tablespace as a designated host directory (implying a certain disk volume) or designated Oracle Automatic Storage Management disk group. Any schema objects assigned to that tablespace then get located in the specified storage location. Tablespaces also provide a unit of backup and recovery. The backup and recovery features of Oracle Database enable you to back up or recover at the tablespace level.

You can create and manage new tablespaces to support your user and application data requirements. As data is added to your database, the tablespace requirements for your database change.

Common Issues Involving Oracle Tablespace Management

Most of the issues involving tablespaces deal with space management, monitoring space and knowing what should be done when particular out of space/unable to extend errors are encountered. The following content lists some of the available documents and known issues involving managing tablespaces, as well as information/issues affecting specific Oracle tablespaces.

It is important to know some basic details when investigating issues with tablespaces such as the following:

SYSTEM Tablespace Issues

The SYSTEM tablespace contains the data dictionary for the entire database. SYSTEM (and SYSAUX from 10g onwards) are mandatory tablespaces that cannot be dropped, cannot be taken offline and are required for normal database functionality. A healthy SYSTEM tablespace is required for the database to operate as expected so storage issues should be avoided though proper maintenance and usage. Also, do not create objects in the SYSTEM tablespace. Here are some known issues involving the SYSTEM tablespace:

SYSAUX Tablespace Issues

The SYSAUX tablespace, introduced in 10g, is always created at database creation. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle database features and products that previously required their own tablespaces, it reduces the number of tablespaces required by the database. It also reduces the load on the SYSTEM tablespace.
The following document is a comprehensive document that covers many of the issues involving the SYSAUX tablespace:

Temporary Tablespace Issues

Temporary tablespaces contain data that persists only for the duration of a user’s session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. The following is a troubleshooting guide specific to Oracle Temporary Tablespaces:

UNDO Tablespace Issues

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. With automatic undo management, the database manages undo segments in an undo tablespace. The following address some of the known issues encountered with Undo tablespaces:

Helpful Articles on UNDO Tablespaces

The following are some useful articles on undo tablespaces:

Tablespace Monitoring Issues

Oracle Enterprise Manager Database Control (Database Control) helps in managing the storage structures within the database.  It can be used to view configuration, size, and status information about tablespaces. Alerts can be received in Database Control when a space usage threshold for a tablespace is reached. The following contain some known issues and recommendations:

"unable to extend ... by %s in tablespace " ( incl. ORA-01650 / ORA-01652 / ORA-01653 / ORA-01654 and ORA-01658 ) Issues

The following documents can be used to diagnose and resolve space management errors of the type "unable to extend ... by %s in tablespace ":

Other ORA- Errors/Known Issues Involving Tablespaces

Drop Tablespace / Datafile Issues

You can drop a tablespace and it's contents (the segments contained in the tablespace) from the database if the tablespace and it's contents are no longer required. From 10g onwards there is also the option to drop a datafile from a tablespace. The following notes outline some issues and restrictions with these commands:

Transportable Tablespace Issues

You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another. Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use Data Pump to transfer only the metadata of the tablespace objects to the new database. The following documents cover many of the questions and issues with Transportable Tablespaces:

Helpful Articles on Tablespaces (How to / Scripts)

How to Articles

Useful Scripts for Tablespaces

Further Diagnostics

If you were not able to resolve the issue with the details provided in this document, please raise a Service Request for further assistance from Oracle Support. Please include the following:
* Collection of the relevant information using selects from the dictionary views, showing the current states and used attributes. Example: Query V$DATABASE, V$INSTANCE
* Alert.log and trace files
* RDA report

References