Managing Tablespaces (Oracle)

Use

You manage tablespaces in your Oracle database 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.

Prerequisites

A tablespace overflow can occur in the following situations:

·        Operations that greatly extend a table

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

·        Poor monitoring

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. With BR*Tools and the Computing Center Management System (CCMS), you can easily monitor your tablespaces.

Procedure

...

       1.      Regularly monitor freespace and rapidly growing objects in the database, using the CCMS or BR*Tools.

If freespace in a tablespace decreases continuously, extend the tablespace in time to accommodate further growth.

Also monitor the database for rapidly growing objects, that is, objects that allocate more and more extents, using the CCMS or BR*Tools.

       2.      Monitor disk space at operating system level

You also need to monitor available disk space at the operating system level. Plan for additional disks in time to be able to accommodate tablespace growth.

       3.      If necessary, make more space available in a tablespace using one of the following approaches:

Ў        Add a data file to extend the tablespace while the database is online to avoid downtime

If no disk space is available and you cannot use the other methods described below, this might cause a situation where the application is partially unusable because insert and update operations cannot continue. If you use BR*Tools for this task, the new file is created according to SAP’s naming conventions.

Oracle also offers a feature to enable automatic extension of the data files of a tablespace – see the autoextend option of the create tablespace and alter tablespace commands. This avoids a tablespace overflow, but only as long as enough disk space is available. SAP does not recommend using this feature at present. Therefore, the approaches below do not take account of this feature.

Ў        Reorganize the tablespace or single objects

It might be possible to free up space in a tablespace by reorganizing the whole tablespace or single objects in that tablespace. This is only true if particular objects or objects in general in a tablespace contain a lot of unused space (for example, after a table has many inserts followed by many deletes).

Ў        Deallocate free space

If space allocated to a table or index has never been used (that is, data has never been written to the data blocks), you can free such space for use by other objects in the tablespace. You can do this online without incurring downtime using the command alter table|index <name> deallocate unused. When a tablespace overflows and there is no disk space available and a reorganization is not possible (for example, because downtime cannot be tolerated), you might be able to continue processing with this approach.

However, remember that the objects for which you have deallocated free space might themselves soon require new extents as they grow with inserts and updates. Therefore, a more permanent solution is to reorganize affected objects when downtime can be tolerated and also, if necessary, extend the tablespace.

This approach cannot free space that was once used, for example, when an insert has been followed by a delete.

SAP recommends the following concerning the above approaches to making more space available in a tablespace:

·         If possible, add a data file instead of reorganizing objects or deallocating free space. If all the above approaches are available to resolve tablespace overflow – that is, if disk space is available to add a new data file and if a reorganization or free space deallocation would free up space in the tablespace – always choose to add a data file and avoid the reorganization or free space deallocation.

·         Note that reorganization (either of individual objects, or of the entire tablespace) without recreating the data files might not be possible if the tablespace is full or very nearly full. The general rule is to reorganize before 90% of available space has been allocated.

Since the reorganization tends to create objects with fewer larger extents and evenly distributed freespace (according to PCTFREE), the end result might be that more space in total is needed than before the reorganization. As a result, it might be impossible to fit all the objects back into the tablespace. To avoid this it is recommended to reorganize a tablespace well before it is full. BR*Tools automatically checks for this condition when preparing for a reorganization. For more information, see Reorganizing Objects (Oracle).

Result

By managing the tablespaces in your Oracle database, you can avoid unplanned downtime due to database objects filling up.