Freespace Check during Reorganization of a Tablesp (SAP Library - Database Administration (Oracle) with SAPDBA)

Freespace Check during Reorganization of a Tablespace

Import check when menu item Reorganize tablespace is selected.

SAPDBA checks whether enough space is available in the data file(s) of the tablespace for the extents of the tables/indexes which are to be reorganized (the check includes the entire storage of the tablespace).

SAPDBA performs this check in the following manner:

Sorts the tables/indexes for reorganization in ascending INITIAL value order. This sequence is stored in the SQL script containing the CREATE TABLE/INDEX commands.
Sorts the data files of the tablespace by size of the freespace that will be available after the objects are deleted, in descending order (at the beginning of the check, this value is identical to the size of the file; this is then reduced by the size of the mounted objects).
Attempts to fit the extents of the tables/indexes (in the given order) in a file. This process starts at the file with the most freespace. If a file with sufficient freespace is found, the table/index is mounted into it. This operation (e.g. step 2 and step 3) is repeated until space has been found in the tablespace for all the objects contained in the SQL script.

SAPDBA assumes that the extents for reorganization can be successfully reimported when the following condition has been met:

The freespace available according to the simulated reorganization (estimated value) is greater than the reserve space.

The reserve space is a freespace requirement that SAPDBA uses in its calculations. This reserve ensures that the import of a reorganized tablespace only fails in the rarest of cases. The reserve corresponds to the size of the largest NEXT extent of the object, but is always at least 120KB.

If you ignore a warning message during reorganization and the import fails, you can extend the tablespace by adding a data file. Then select the SAPDBA restart function to continue with the reorganization.

If SAPDBA reports at the very start that there is not enough space for the requested reorganization, we recommend extending the tablespace and/or using the option Reduce object size: yes and then restarting the reorganization. If the SAPDBA check fails, the tablespace probably would have had to be extended soon anyway.