Space Management with Oracle

Purpose

This section looks at space management (including reorganization) of database objects (that is, tables and tablespaces). If you neglect space management, this can lead to downtime due to normal database growth when database objects fill up. If this happens, applications cannot write to the database and you have to quickly make more space available. You might need to bring down the SAP system to tune and configure the database. Therefore, it is much better to anticipate the problem by monitoring and pro-actively managing the disk space in your database.

SAP recommends you to manage space on your Oracle database using the Computing Center Management System (CCMS) in the SAP system and Space Management with BR*Tools. You need to monitor regularly and occasionally take timely action to avoid the problem leading to downtime.

Situations when you need to reorganize include the following:

·        Tablespace overflow (or data file freespace shortage)

·        Fragmentation

·        Chained rows

·        Maximum number of files reached

The most likely events to require reorganization are index fragmentation, tablespace overflow, and chained rows.

Avoid reorganization if possible

SAP strongly stresses that you should avoid reorganizations wherever possible. You can achieve this by correct configuration and sizing of the database together with proper monitoring.

The problems of tablespace overflow and fragmentation are more likely to occur in the following tablespaces in an SAP system (add "D" for data tablespace or "I" for index tablespace to the end of each tablespace name):

Tablespace

Comment

PSAPBTAB

Transaction data tables. Objects in this tablespace might expand very rapidly.

PSAPSTAB

Master data tables. Objects in these tablespaces might expand very rapidly.

PSAPCLU

Clustered tables, such as financial tables. Objects in these tablespaces might expand very rapidly.

PSAPPOOL

Pool tables, containing customization tables.

PSAPPROT

Spool (that is, print) requests, protocols

Pay special attention to the following tablespaces in certain circumstances:

·        PSAPROLL

Watch this tablespace if you are running a large export or reorganization, background jobs, or if your applications have high transaction rates and few commit points. The tablespace contains the rollback segments and these might be too small to handle large transactions for a particular installation.

·        PSAPTEMP

If you are running a large import or reorganization, you should closely watch this tablespace since it is used to store temporary objects, for example, objects to sort data for a “create index” operation.

For more information about how to monitor these tablespaces see Computing Center Management System (CCMS). For more information about when to perform the reorganization, see "reorganize your database" below in the process flow.

Process Flow

...

       1.      You manage tablespaces, to avoid tablespace overflow.

       2.      You check for fragmentation and manage tables and indexes, to avoid fragmentation.

       3.      You manage database blocks, to avoid chained rows.

       4.      You manage files, to avoid overflow.

       5.      You manage database files to avoid poor distribution and poor disk input/output (IO).

       6.      If necessary, you reorganize database objects.

Result

By managing the space in your Oracle database, you can avoid unplanned downtime due to database objects filling up.

See also:

You can find more information about Oracle with the SAP system on SAP Service Marketplace at:

service.sap.com/dbaora