Space Management

This section discusses how the Oracle database is set up to meet the space management needs of Oracle E-Business Suite. It provides information on tablespaces, firstly outlining the basic tablespaces required, then discussing the traditional tablespace structure used to support Oracle E-Business Suite products, and finally describing the tablespace model that is used as standard with Oracle E-Business Suite Release 12.
Important: Oracle E-Business Suite Release 12 requires an Oracle database block size of 8K. No other block size may be used.

Introduction to Tablespaces

An Oracle 11g database always requires the following tablespaces to be available:

• System Tablespace - This tablespace holds data dictionary tables owned by the SYS account, and is created when the database is installed.
  
• Undo Tablespace - This tablespace holds undo (rollback) information that is used to track database changes until they are either committed or undone (rolled back).
  
• Temporary Tablespace - Temporary tablespaces are used to sort data while it is being processed. It is possible to use a single temporary tablespace, typically called TEMP, for all Oracle E-Business Suite products. Alternatively, separate temporary tablespaces can, if desired, be created for individual products. Since users access Oracle E-Business Suite objects through the APPS schema, the temporary tablespace for that schema (initially the same as that for the Oracle Application Object Library) is used by all products.

The traditional Oracle E-Business Suite tablespace model employed separate tablespaces for a product's tables and indexes. The resulting tablespaces were named by appending 'D' for data or 'X' for an index to the product's short name or Oracle schema name. For example, APD was the tablespace for Oracle Payables data, and APX was the tablespaces for Oracle Payables indexes.
Note: For further information about tablespaces, see the Oracle Database Administrator's Guide 11g.
Employing separate table and index tablespaces for each product made it easier maintain products, and helped to improve database performance. However, with an increasing number of products, this model could easily require several hundred product tablespaces, plus a system tablespace, undo (rollback) tablespace, and temporary tablespace.
In addition, the traditional tablespace model used a database sizing factor to set the extent sizes for an Oracle E-Business Suite product's tables and indexes. The value of this factor was a percentage of the typical estimated growth rate for Oracle E-Business Suite database objects. The sizing factor affected only the size of subsequent extents, as determined by the NEXT database object creation parameter. Most objects were defined with small first extents and larger additional extents.
During installation, Rapid Install provides the option of distributing tablespaces across different disks, to reduce disk head contention and improve overall system performance. In addition to this, many production systems utilize sophisticated disk and volume management technologies at operating system level to further enhance performance.

Oracle Applications Tablespace Model

Oracle E-Business Suite Release 12 utilizes as standard a modern infrastructure for tablespace management, the Oracle Applications Tablespace Model (OATM). The Oracle Applications Tablespace Model is similar to the traditional model in retaining the system, undo, and temporary tablespaces. The key difference is that Oracle E-Business Suite products in an OATM environment share a much smaller number of tablespaces, rather than having their own dedicated tablespaces.
Oracle E-Business Suite schema objects are allocated to the shared tablespaces based on two main factors: the type of data they contain, and I/O characteristics such as size, life span, access methods, and locking granularity. For example, tables that contain seed data are allocated to a different tablespace from the tables that contain transactional data. In addition, while most indexes are held in the same tablespace as the base table, indexes on transaction tables are held in a single tablespace dedicated to such indexes.

The Oracle Applications Tablespace Model provides a variety of benefits, summarized in the list below and discussed in more detail later:

• Simplifies maintenance and recovery by using far fewer tablespaces than the older model.
  
• Makes best use of the restricted number of raw devices available in Oracle Real Applications Cluster (Oracle RAC) and other environments, where every tablespace requires its own raw device.
  
• Utilizes locally managed tablespaces, enabling more precise control over unused space and hence reducing fragmentation.
  
• Takes advantage of automatic segment space management, eliminating the need for manual space management tasks.
  
• Increases block-packing compared to the older model, reducing the overall number of buffer gets and improving runtime performance.
  
• Maximizes usefulness of wide disk stripe configurations.

The Oracle Applications Tablespace Model uses locally managed tablespaces, which enables extent sizes either to be determined automatically (autoallocate), or for all extents to be made the same, user-specified size (uniform). This choice of extent management types means that locally managed tablespaces offer greater flexibility than the dictionary-managed tablespaces used in the traditional tablespace model. However, when using uniform extents with locally managed tablespaces, the extent size must be chosen with care: too small a size can have an adverse effect on space management and performance.
A further benefit of locally managed tablespaces, and hence use of OATM, is the introduction of automatic segment space management, a simpler and more efficient way of managing space within a segment. It can require more space, but eliminates the need for traditional manual segment space management tasks such as specifying and tuning schema object storage parameters such as PCTUSED. This and related storage parameters are only used to determine space allocation for objects in dictionary-managed tablespaces, and have no meaning in the context of locally managed tablespaces.
Automatic segment space management is self-tuning, so can take into account an increase in the number of users. A further benefit in Oracle Real Applications Cluster (Oracle RAC) environments is dynamic affinity of space to instances, which avoids the hard partitioning of space inherent with the traditional use of free list groups.

Table 3-1 OATM Tablespace Types and Contents

Tablespace Type	Tablespace Contents
Transaction Tables	Tables that contain transactional data.
Transaction Indexes	Indexes on transaction tables.
Reference	Reference and setup data and indexes.
Interface	Interface and temporary data and indexes.
Summary	Summary management objects, such as materialized views, and other objects that record summary information.
Nologging	Materialized views that are not used for summary management and temporary objects.
Advanced Queueing	Advanced Queuing (AQ) tables and indexes.
Media	Multimedia objects, such as text, video, sound, graphics, and spatial data.
Archive	Tables that contain archived purge-related data.
Undo	Automatic Undo Management (AUM) tablespace. Undo segments are equivalent to rollback segments when AUM is enabled. See note below.
Temp	Temporary tablespace for global temporary table, sorts, and hash joins.
System	System tablespace used by the Oracle database.

In Oracle database server releases prior to Oracle9i, undo space management was performed using rollback segments. For clarity, this method is now referred to as manual undo management. Its successor, automatic undo management is based on the use of a small number of undo tablespaces, in contrast to the larger number of variously-sized rollback segments typically used in manual undo management.