Check Tablespaces
Purpose
It is important to regularly monitor the tablespaces in your Oracle database, because you might need to extend or reorganize them. In either case, database performance can worsen.
The database monitor of the Computing Center Management System (CCMS) also helps you check tablespaces. For more information, see
Monitoring the Oracle Database.
Prerequisites
These are the two main cases that you need to distinguish when checking tablespaces:
You must always extend a full tablespace, otherwise it overflows if more data is added. This process describes how to check tablespaces for overflow.
If the tablespace has sufficient free storage space, it is probably a fragmentation of free storage space that is causing the storage problems. This occurs only if DROP operations are performed frequently. Database objects that are frequently deleted (that is, dropped) and created again should be separated.
You can solve the storage problems by reorganizing the fragmented tablespace or the fragmented tables and indexes that it contains. An extension of the tablespace is then unnecessary.
However, a reorganization is time-consuming and complicated. Therefore, SAP recommends that you extend the tablespace rather than reorganizing it. Reorganize a tablespace only if one or more of the following conditions apply:
For more information on when database limitations require you to reorganize the database, see
If extending a tablespace causes your database to approach the limit determined by the operating system for the number of files, we recommend you to extend then reorganize the tablespace or to extend the tablespace during the reorganization. However, a reorganization is probably unnecessary because the maximum possible number of data files is very high (at least 254 files are available).
Process Flow
Result
By checking the tablespaces regularly, you maintain a high level of database performance.
If your checks indicate that it is necessary, you now
extend the tablespace.