Entering content frameProcedure documentation Checking Freespace for Tablespaces Locate the document in its SAP Library structure

Use

You can use SAPDBA for Oracle to check the freespace for tablespaces. The information you see helps you to decide whether to extend or reorganize a tablespace.

Prerequisites

You have configured and started SAPDBA. Refer to Getting Started with SAPDBA.

Procedure

  1. Choose Tablespace Administration ® Free space and fragmentation of all tablespaces.

    2. SAPDBA displays details of the space situation for all tablespaces in the database. The values Total , Allocated , and Largest are in KB.

  2. Check the following information in particular:

These columns refer to the reserved storage space in kilobytes and as a percentage of the tablespace size.

When %-Alloc. is more than 90 percent, the tablespace might soon overflow. Therefore, you need to extend it by adding a new data file as soon as possible. SAPDBA lists such critical tablespaces at the end of the report.

Caution

Do not try to solve this problem by reorganizing the database. A reorganization only defragments the complete storage space. This means that the fragmented used storage space and fragmented free storage space are put together. That is, the storage space is reorganized, but no additional space is gained. In fact, additional space might even be required.

Always extend a tablespace when there is little space left.

These columns refer to the number of freespace areas and the size of the largest freespace area.

If the percentage of the storage space used is relatively low, check these columns to see whether the storage problems in a tablespace are due to the fragmentation of free storage space.

If the number of fragments is high and the largest fragment is small (smaller than 1 MB), the cause of the tablespace overflow is probably fragmentation. In this case, it might be useful to reorganize the fragmented tablespace (or particular highly fragmented tables and indexes in it) instead of extending it.

Recommendation

However, note that you can usually solve this problem by adding a new file, since there are normally enough data files still available before the limit is reached. If possible, avoid reorganization because it is more complicated and time-consuming.

Result

If you have identified a tablespace that needs further investigation, refer to Checking Freespace for Objects in Tablespace.

Leaving content frame