Managing Tablespaces


You manage tablespaces in your Oracle database as part of Space Management. You can extend, create, drop, and alter tablespaces.

You especially need to avoid tablespace overflow, which is when a tablespace runs out of freespace in the allocated file or files. This happens when an object requires a new extent but there is either no freespace or insufficient freespace in the tablespace.

You do not need to start a backup immediately after structural changes to the database such as tablespace extension, tablespace create, or drop. If the database crashes before a backup was performed BRRECOVER can reapply the changes during the recovery procedure based on information stored in the BRSPACE structure change log, struc<DBSID>.log.


Tablespace overflow can occur in the following situations:

·        Operations that greatly extend tables in the tablespace

Be sure to plan certain operations (for example, client copy or batch input) carefully, because they might extend tables excessively.

·        Poor monitoring of the tablespace

During normal operation, database objects (that is, tables and indexes) grow steadily. Be sure to monitor the database, anticipate growth, and make sure there is always enough disk space available.



       1.      Extend a tablespace in one of the following ways:

Ў        Add a data file to the tablespace.

Use this method when either of the following conditions applies:

§         The existing data files cannot be resized because there is not enough disk space available on the disks where the files are located

§         The existing data files have reached their maximum size.

For more information on how to add a data file, see Extend a tablespace with BR*Tools.

The following graphic shows the effect of adding a data file:

Ў        Set the AUTOEXTEND option for the data files in the tablespace.

Ў        Resize an existing data file to provide more space.

For more information on setting the AUTOEXTEND option and resizing a data file, see Managing Data Files.

       2.      Create a new tablespace for the following reasons:

Ў        To prepare for an upgrade, when you need to create a new tablespace for the SAP software of the new release.

Ў        To prepare for an online reorganization of a tablespace, for example, to switch from a dictionary managed to a locally managed tablespace.

Ў        After you have exported tables from a tablespace and you want to relocate the tablespace to a new disk volume for the import.

Since creating a tablespace is a structural change to the database, BRSPACE:

Ў        Creates a control file backup in the directory $SAPDATA_HOME/sapreorg/<encoded timestamp> before and after the procedure.

Ў        Logs the action in the BRSPACE Structure Change Log.

When you create a new tablespace, you can specify whether the tablespace file:

·         Has the AUTOEXTEND option set and, if so, the increment and maximum size.

·         Is on a raw device or in the file system. For more information, see “Prerequisites” in Space Management.

       3.      Drop a tablespace, for example, when the tablespace is no longer required after an upgrade.

BRSPACE only lets you drop an empty tablespace, unless you specify the –f|-force option.

Since dropping a tablespace is a structural change to the database, BRSPACE:

Ў        Creates a control file backup in the directory $SAPDATA_HOME/sapreorg/<encoded timestamp> before and after the procedure.

Ў        Logs the action in the BRSPACE Structure Change Log.

BRSPACE removes all subdirectories for the data files when it drops the tablespace.

       4.      Alter a tablespace for a number of reasons:

Ў        Set a tablespace online or offline:

For systems with Multiple Components in One Database (MCOD), you can set the tablespaces PSAP<SCHEMA_ID>belonging to one schema user SAP<SCHEMA_ID> offline for maintenance. This does not affect tablespaces from other SAP systems with a different SAP<SCHEMA_ID>.

The SAP system can only function if all the tablespaces belonging to the schema user are online. Make sure that you find the cause if Oracle has automatically set a tablespace offline, that is, without any intervention from you.

An example of this is when Oracle receives an operating system I/O error when writing to a data file. In this case, Oracle can set the tablespace offline to prevent corrupt blocks.

Ў        Set or reset the backup status

BRBACKUP sets and resets the backup status for a tablespace during and after an online backup.

If BRBACKUP fails during an online backup, tablespaces might be left in backup status. The database system check can report this error when it raises the condition TABLESPACE_IN_BACKUP. For more information, see BRCONNECT Default Conditions for Database Administration.

Use BR*Tools to reset the backup status if you are sure that:

§         An online backup has crashed

§         There is currently no online backup running

Ў        Coalesce free extents

This combines contiguous extents with free space into a single large extent within a tablespace. It consolidates the storage structure of the tablespace and can improve performance.

Although the database system check automatically coalesces tablespace free extents, you can perform this action on a one-off basis if a large amount of data was deleted. For example, if you have deleted a client or archived data, you can perform this action immediately afterwards.

Ў        Rename tablespace

With this action, available from Oracle 10g,  you can rename a tablespace following a reorganization. It enables you to drop the old tablespace and assign its name to the new tablespace.