Show TOC

Space Management with OracleLocate this document in the navigation structure

Use

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 proactively managing the disk space in your database.

Recommendation

We recommend 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.

Note

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 before SAP Web Application Server Release 6.10 (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

Caution

The above table shows the old tablespace layout. In newer systems, all data is now contained in a single common tablespace PSAP<SCHEMA_ID>.

Pay special attention to the following tablespaces in certain circumstances:

  • PSAPROLL/PSAPUNDO

    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.

    Note

    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
  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.