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:

SQL> select tablespace_name, contents, allocation_type, extent_management, segment_space_management from dba_tablespaces;

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:

Note 463226.1 Size of Sys.C_obj#_intcol# cluster in system tablespace is growing
Note 943094.1 SYSTEM Tablespace Using MSSM Is Abnormally Growing And The Clusters C_TOID_VERSION# and/ or C_OBJ#_INTCOL# Are Using Most Of The Space.

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:

Note 1399365.1 Troubleshooting Issues with SYSAUX

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:

Note 1524594.1 Master Note: Troubleshooting 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:

Note 460481.1 Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace
Note 467872.1 TROUBLESHOOTING GUIDE (TSG) - ORA-1555
Note 413732.1 Full UNDO Tablespace In 10gR2

Helpful Articles on UNDO Tablespaces

The following are some useful articles on undo tablespaces:

Note 268870.1 How to Shrink the datafile of Undo Tablespace
Note 461480.1 FAQ -Automatic Undo Management (AUM) / System Managed

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:

Note 849498.1 Oracle Recommended Patches For Tablespace Monitoring using the Tablespace Space Used % metric
Note 403264.1 Troubleshooting a Database Tablespace Used(%) Alert problem
Note 870602.1 DBA_OUTSTANDING_ALERTS NOT SHOWING ALERT FOR TABLESPACE THAT EXCEEDED THRESHOLD
Note 387868.1 Database Tablespaces Page shows different Tablespace Usage values than Tablespace Used(%) Metric

Oracle 12C:

Note 1596545.1 EM 12c : How to Exclude TEMP and UNDO Tablespaces From The Tablespace Used (%) Metric To Avoid Event Alerts ?

<Note 1541511.1> How to Test Whether the "Tablespace Space Used (%)" Metric Alert is Working as Expected for a Database Instance or Cluster Database Instance in Enterprise Manager 12c?

Note > The value of the metric Tablespace Space Used (%) is incorrect in Enterprise Manager Grid Control 11g or Cloud Control 12c

Note > Troubleshooting The Metric Alert for Database Instance "Tablespace Space Used (%)" in Enterprise Manager 12c Cloud Control

<Note 1532334.1> Troubleshooting a "Database Tablespace Space Used (%)" Alert issue in 12c Cloud Control

"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 ":

Note 1025288.6 TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors
Note 793380.1 ORA-1652 Error Troubleshooting
Note 1267351.1 TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment
Note 19047.1 OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s
Note 151994.1 Overview Of ORA-01653: Unable To Extend Table %s.%s By %s In Tablespace %s
Note 146595.1 Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s
Note 33290.1 OERR: ORA 1658 "unable to create INITIAL extent for segment in tablespace %s"

Other ORA- Errors/Known Issues Involving Tablespaces

Note 1417751.1 The Curious Case of the ORA-959 And The _$deleted$ Tablespace
Note 104280.1 ORA-01144 on Create Tablespace or Resize of Datafile
Note 970324.1 ORA-01536: space quota exceeded for tablespace along with DBMS_COMPRESSION.GET_COMPRESSION_RATIO
Note 380473.1 Resize Does Not Work Although Query Shows Free Space Available ORA-03297
Note 1174483.1 ORA-25183 When Dropping Tablespace Including Contents
Note 10177856.8 Bug 10177856 - DBMS_METADATA.get_ddl('tablespace', ...) wrongly includes tempfiles and online redo-logfiles
Note 12755116.8 Bug 12755116 - ORA-959 after DEFAULT tablespace of schema dropped / renamed
Note 13833511.8 Bug 13833511 - M000 process spins after a tablespace is renamed and new tablespace is created with original name
Note 13066863.8 Bug 13066863 - Read only tablespaces still show in controlfile trace after successful DROP
Note 10302581.8 Bug 10302581 - START_REDEF_TABLE creates index in SYSTEM tablespace

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:

Note 389467.1 Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted
Note 1050261.1 Unable to Drop a Datafile From the Tablespace Using Alter Tablespace Command
Note 781225.1 DROP Datafile And Its Restrictions

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:

Note 1166564.1 Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues
Note 733824.1 How To Recreate a database using TTS (Transportable TableSpace)

Note > Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable

Helpful Articles on Tablespaces (How to / Scripts)

How to Articles

Note 147356.1 How to Move Tables from One Tablespace to Another
Note 1029252.6 How to Resize a Datafile
Note 111316.1 How to 'DROP' a Datafile from a Tablespace
Note 860310.1 How To Convert Tablespace from Manual To Automatic Segment Space Management
Note 243332.1 10G: Rename Tablespace Enhancement
Note 270061.1 How To Rename a Tablespace and Avoid Certain Errors
Note 789023.1 How to drop or rename _$deleted tablespaces
Note 216683.1 How to Recover Data from a Tablespace When One or Several Datafiles are Lost
Note 1360446.1 How to Calculate the Size of the Database

1621131.1> How to let the PDB use Global temporary tablespace instead of local temporary tablespace

Useful Scripts for Tablespaces

Note 1380813.1 Useful SQL Queries for TableSpace Management
Note 1377458.1 How to Display Storage Map for Database | Tablespace | Datafile Storage
Note 1019709.6 Script to Report Tablespace Free and Fragmentation
Note 1019712.6 Show Tablespace Quota Used by User
Note 1019999.6 Script: To List Tablespace, Datafiles and Free Space
Note 1020182.6 Script to Detect Tablespace Fragmentation
Note 1020090.6 Script to Report on Space in Tablespaces
Note 1019712.6 SCRIPT: Show Tablespace Quota Used by User
Note 1047952.6 Script to Determine Objects Per Tablespace

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

NOTE:868955.1 - Get Proactive - Oracle Health Checks - Installation, troubleshooting, catalog and more.
NOTE:1493350.1 - Master Note: Overview of Oracle Tablespace Management