Freespace Check for a Reorganization 

Use

• Reorganizing a single table or index
• Reorganizing a list of tables and indexes
• Reorganizing a tablespace
• Reorganizing a tablespace and data files

SAPDBA takes reserve space into account when calculating the space required for the reorganized table or index. This reserve prevents the reorganization from failing in the rare case that the space requirements of the reorganized table or index are greater than before the reorganization. This can occur, for example, if the PCTFREE values are relatively high and the PCTFREE areas are quite full. These areas are freed during reorganization. Any additional NEXT extents are requested at this time.

The reserve space corresponds to the size of the NEXT extent of the affected table or index, but is always at least 120KB.

• All the extents to be generated according to the number of MINEXTENTS for all the tables to be reorganized.
• All other NEXT extents of tables to be expected during the data import (this number is determined from the old total size allocated or from the total size of the tables determined by the option ReduceOb: yes, which means "reduce object size").
• All expected INITIAL and NEXT extents of all indexes to be reorganized, calculated as in the previous two points.
• Reserve space, equal to the size of the largest NEXT extent or at least 120 KB.

This description applies to the export/import type of reorganization. If you are using the CREATE TABLE AS SELECT method, see "Reorganizing Using Create Table as Select ..." below.

1. SAPDBA checks whether the extents specified by MINEXTENTS for all the tables to be reorganized fit into the data files in the tablespace.
2. SAPDBA also checks whether there is enough space for all the NEXT extents that are expected during the reorganization.
3. For the above checks, SAPDBA determines the size of the freespace areas that will be available in the individual data files when the table or index is deleted.
4. If there is not enough freespace, SAPDBA does not perform the reorganization. In this case, try either or both of the following before repeating the reorganization:

5. If not enough reserve space (see "Prerequisites" above) is available, you can still start the reorganization by simply ignoring the warning message. Alternatively, you can cancel the reorganization.
6. If you are reorganizing an index or an index is reorganized as part of a table reorganization, SAPDBA checks whether sufficient space is available for sorting the index in tablespace PSAPTEMP . If tablespace PSAPTEMP has less space than twice the size of the largest index for reorganization, SAPDBA issues a warning message.

This description applies to the export/import type of reorganization. If you are using the CREATE TABLE AS SELECT method, see "Reorganizing Using Create Table as Select ..." below.

1. 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:

1. Sorts the tables or indexes for reorganization in ascending INITIAL value order. This sequence is stored in the SQL script containing the CREATE TABLE/INDEX commands.
2. 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, but this is then gradually reduced by the size of the added objects).
3. Attempts to fit the extents of the tables or indexes (in the given order) in a file. This starts at the file with the most freespace. If a file with sufficient freespace is found, the table or index is added to it.
4. The previous two steps are repeated until space has been found in the tablespace for all objects to be reorganized.

1. SAPDBA performs the checks described above in "Reorganizing a Tablespace."
2. SAPDBA checks whether the selected file system has sufficient freespace for the new files of the tablespace. SAPDBA first suggests the file system containing file <tablespace name>.data1 . The check considers the freespace that will be available once the tablespace is deleted.
3. SAPDBA specifies which directories the new data files for the tablespace are to be stored in (file numbering starts again with the suffix "1") and asks whether you want to specify additional paths for the data files. If necessary, you can then specify a path in a file system with more space. Both the available sapdata<n> directories and the possible raw devices can be selected with a list that can be displayed by SAPDBA. In this case, SAPDBA repeats the import check and then performs the reorganization.

Follow the SAP Naming Conventions for Tablespaces and Data Files wherever possible.



If the files are not in a standard SAP directory, SAPDBA proposes the directory <SAPDATA_HOME>/sapdata<n>/<tablespace name>_<file number> , which is a symbolic link to the specified directory.

4. If a raw device is to be used for the new data file, SAPDBA automatically generates a symbolic link to the specified raw device in directory sapraw . In most cases SAPDBA suggests the old distribution of the tablespace on the files (since the raw devices have a fixed size). You might have to change this suggestion manually.

When you reorganize with CREATE TABLE AS SELECT or in deferred mode, SAPDBA reorganizes the objects one after the other. Sufficient disk space must be available during reorganization for both the old and the new objects. SAPDBA checks each time whether there is enough space to regenerate the largest object to be reorganized, and always reorganizes the largest object first.

In exceptional cases – for example, when the objects to be reorganized are very fragmented or free space is relatively short – Oracle errors can occur when generating the new objects during the reorganization of a list of objects or a whole tablespace. The affected object is not reorganized. You can restart the reorganization and then reorganize the affected object later on its own.