Purpose
Before you start to reorganize your Oracle database using SAPDBA, you need to prepare properly.
Prerequisites
Before you start to prepare the reorganization, you consider the following:
It can take a long time to reorganize large quantities of data, depending on the speed of your hardware. Consider the following when scheduling the reorganization:
We recommend reorganizing immediately after opening the database in restricted session mode, because this means there are no locks on database objects.
It is especially important to improve performance when
reorganizing certain tablespaces, such as PSAPPOOLD , PSAPSTABD , and PSAPBTABD .If your host has several CPUs, you can save time by
reorganizing in parallel.Also check the main log
reorg<DBSID>.log to see whether a comparable operation has already run. This enables you to better estimate the time required.If possible, plan your reorganizations in advance, to enable you to perform them together. This minimizes disruptions to productive operation.
SAPDBA can only reorganize tablespaces for which the data files conform to the SAP Naming Conventions for Tablespaces and Data Files. Be aware of the special features when working with raw devices.
If you are reorganizing several objects specified in a list or tablespace, make sure that your file system (for the export/import method) or the affected tablespace (for the create as select method) have enough space for the objects to be reorganized. SAPDBA also checks this for you.
If you want to reorganize a table or tablespace (without also reorganizing data files), make sure that enough free storage space is available in the relevant tablespace before the reorganization. Approximately 5% to 10% of the tablespace size is necessary. To check this, see:
If your checks have revealed a large amount of free space in the objects, you can reorganize so that the storage parameters are adapted, or you can just
change the storage parameters without also reorganizing.Process Flow
Result
Now you are ready to start the
reorganization.