Managing Tablespaces (Oracle)

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.

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.

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.

• 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 for an offline reorganization 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 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.

  Recommendation

  We recommend 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 almost 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 we recommend you to reorganize a tablespace well before it is full.

    For more information, see Reorganizing Objects (Oracle) .