Reorganization Type (SAP Library - Database Administration (Oracle) with SAPDBA)

Reorganization Type

There are several types of database reorganization. Select the correct reorganization type - for example, if a specific table is causing space problems, reorganize only that table, and not the entire tablespace.

The following figure shows the different reorganization types. It is based on a tablespace consisting of three data files with two fragmented tables and a fragmented freespace.

Table/index reorganization: The quickest and simplest type of reorganization is the reorganization of an individual table or an index in a tablespace.

This type of reorganization merges the extents occupied by the table or the index to one extent (using the default value compress extents: yes). In addition, adjacent free storage fragments in the entire tablespace are merged to form one closed area. The Oracle database system does this with its own system monitor.

Depending on the tablespace fragmentation, a table/index reorganization with the option Compress extents: yes may not be possible, even though the total freespace is larger than the table or the index. SAPDBA can detect and display this situation.

See Reorganization of a Single Object.

Reorganization of a list of tables and indexes: Specification of an ASCII file (list name) containing one object per line ([ owner.]<object name>) that is to be reorganized. If another owner is not specifically defined, sapr3 is the owner of these objects. This ASCII file must have been created in the working directory.

Each object listed in the list file is reorganized as described in Table/index reorganization.

See Reorganization of a List of Objects.

Tablespace reorganization: All tables and all indexes of a tablespace are merged to one extent per object. The freespace in the tablespace is also merged in one fragment per data file (not when using the options Create table ... as select or Index rebuild).

See Reorganization of a Tablespace.

Tablespace reorganization with data files: In addition to the simple tablespace reorganization, this reorganization merges the data files of a tablespace to one data file, if the resulting file is smaller than 2 GB (limit of most operating systems). Otherwise SAPDBA recommends writing the contents of the old files to a number of files of equal size. This number is the minimum number of files required to guarantee that each single file size is smaller than 2 GB. All files are written to the same directory (name in accordance with the SAP naming conventions).

The old data files (5 files) have a total of 4.8 GB. SAPDBA recommends creating three new data files with 1.6 GB each. You can also change this default value, for example to 2x1.9 GB and 1x1.0 GB or 4x1.2 GB etc.

This reorganization type is the most complicated, and should only be performed rarely - if at all.

See Reorganization of a Tablespace with Data Files.

Moving/renaming the data files of a tablespace: If you only want to change the data file allocation for a tablespace, you do not have to reorganize the corresponding tablespace. In this case, use the option Move/rename data files of a tablespace .

See Moving/Renaming the Data Files of a Tablespace

Defragmentation

SAPDBA always performs a defragmentation of the entire database, regardless of the type of reorganization. Once all the tables and indexes to be reorganized have been deleted, the freespace now existing for the entire database is defragmented. Free fragments which are physically directly next to each other are combined within all the data files. The deleted tables and indexes are then recovered.