Choosing a Reorganization Type (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

Choosing a Reorganization Type

Use

There are several types of Oracle database reorganization. This section helps you choose the correct reorganization type. For example, if a specific table is causing space problems, you need only reorganize that table, not the entire tablespace.

Prerequisites

The following graphic is based on a tablespace consisting of three data files with two fragmented tables and fragmented freespace:

Main Reorganization Types for an Oracle Database

This graphic is explained in the accompanying text

SAPDBA always defragments the tablespaces that are influenced by the reorganization. Once all the tables and indexes to be reorganized have been deleted, SAPDBA defragments the freespace of the tablespace, combining free fragments that are physically directly next to each other within all the data files. SAPDBA then recreates the deleted tables and indexes.

Procedure

You choose the type of reorganization most appropriate to your needs:

Reorganization of a single object (that is, table or index)

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

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

Reorganization of a list of objects (that is, a list of tables and indexes)

In this type of reorganization SAPDBA reorganizes each object in the list file as described in the previous option, Reorganization of a Single Object.

Reorganization of a tablespace

This type of reorganization merges all tables and all indexes of a tablespace to one extent per object. The freespace in the tablespace is merged into one fragment for each data file, but not when using the options Create table ... as select or Index rebuild.

Reorganization of a tablespace with data files

This reorganization type is the most complicated and we recommend that you only perform it rarely. SAPDBA proceeds as follows:

It performs the functionality described in the previous option Reorganization of a Tablespace.
If the size of the resulting file is:

Smaller than 2 GB (this is the limit of most operating systems), SAPDBA merges the data files of a tablespace to one data file.

Larger than 2 GB, SAPDBA recommends writing the data to files smaller than 2 GB. SAPDBA writes all files to the same directory, following SAP naming conventions.

Example

Five files require 4.8 GB altogether. SAPDBA recommends creating three new data files with 1.6 GB each. You can change this recommended value, for example, to 2 x 1.9 GB and 1 x 1.0 GB, or 4 x 1.2 GB, and so on.

Moving or renaming the data files of a tablespace

This procedure changes the data file allocation for a tablespace without actually reorganizing the tablespace.

Resizing the Data Files of a Tablespace

This procedure increases or decreases the size of data files of the tablespace.