Oracle Applications Tablespace Model - OATM

Oracle Applications Tablespace Model - OATM

The Oracle Applications Tablespace Model was another long awaited feature that got introduced in 11.5.10.Prior to 11.5.10 by default each of the oracle applications product would have two dedicated tablespace holding the data element and the other for storing the index eg GLD (For General Ledger base tables) and GLX (For indexes relation to the General Ledger product).This easily resulted in some 300 odd tablespaces to manage apart from the system, temp and the rollback tablespaces.

In the new Oracle Applications Tablespace Model (OATM) all these product related tablespaces have been consolidated in two main tablespaces one for holding the base tables and the other for holding the related indexes. Apart from these two tablespace you have an additional ten tablespaces including system tablespace undo tablespace and the temporary tablespace. Thereby reducing the total number of tablespace in the OATM to twelve.

Apart from the obvious ease of management and administration with a reduced number of tablespace being involved the OATM also provides benefits like efficient space utilization. This is achieved by supporting locally managed tablespaces as opposed to the dictionary managed tablespace in the previous model.

OATM also supports uniform extent allocation and auto allocate extent management. In uniform extent management all the extents have the same size and result in less fragmentation. Auto allocate extent management allows the system to determine the extent sizes automatically.

OATM also provides additional benefits when implementing Real Application Clusters (RAC) in Oracle Applications.

Under the OATM the following twelve tablespaces are created as a default.
• APPS_TS_TX_DATA - This tablespace hold the translational tables of all Oracle Applications products. For example the GL_JE_HEADERS will be a part of APPS_TX_DATA.
• APPS_TS_TX_IDX - All the indexes on the product tables are kept under this tablespace.
• APPS_TS_SEED - The seeded data that is setup and reference data tables and indexes form this tablespace. For example your FND_DATABASES table would reside in the APPS_TS_SEED tablespace.
• APPS_TS_INTERFACE - All the interface tables are kept in this tablespace for example the GL_INTERFACE table.
• APPS_TS_SUMMARY - All objects that record summary information are grouped under this tablespace.
• APPS_TS_NOLOGGING - This tablespace contains the materialized views that are not used for summary purposes and other temporary
object that do not require redo log entries.
• APPS_TS_QUEUES - With the support for advanced queuing in Oracle Applications, the advanced queue tables and related objects form a part of this tablespace.
• APPS_TS_MEDIA - This tablespace holds multimedia objects like graphics sound recordings and spital data.
• APPS_TS_ARCHIVE - Tables that are obsolete in the current release of Oracle Applications 11i are stored here. These tables are preserved to maintain backward compatibility of custom programs or migration scripts.
• UNDO - The undo tablespace is used as automatic undo management is enabled by default in 11.5.10.This acts as a replacement to red log files.
• TEMP - The Temp tablespace is the default temporary tablespace for Oracle Applications.
• SYSTEM - This is the SYSTEM tablespace used by the Oracle Database.

For all new installation of 11.5.10 OATM is available as a default install. For prior applications system you can migrate your existing tables spaces to the oracle applications tablespace model.

For this purpose you have the Tablespace Migration Utility(TMU) which is available as a patch (3381489).

The TMU is a Perl based interactive utility that helps in migrating the oracle applications schemas to implement the OATM.