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.
For additional information refer to the Oracle Applications Tablespace Migration Utility User Documentation.
Comments
Hi Sam,
I like your new blog, its quite easy to on eyes.
I have been on 11.5.10 for almost an year now, Yes we were early adopters. But I never knew about OATM.
Do you recommend we create custom indexes in APPS_TS_TX_IDX ?
Thanks
Anil Passi
http://appstechnical.blogspot.com/
Posted by: Anil Passi | October 13, 2006 01:51 AM
Anil,
Thanks.
In fact you may not be on OTAM if you have upgraded from aprior release and noty opted for Tablespace Migration.
In my org we are implementing one of oracle corp's biggest clients whith rolll outs in 23 countries across the world.Currently the developers are creating custom index in the APPS_TS_TX_IDX tbs only,previously they used the APPS_TS_TX_DATA tablespace.
We are further planning to move out to a custom tablespace for all custom objects.
Sam
Posted by: Sam | October 13, 2006 10:57 AM
Hi Sam,
This is very interesting. I will point this out to our DBAs.
If I was to ask you the biggest advantage of moving to OATM, what according to you is that going to be. Apart from maintanance ease & disk sizing benefits,will this give any leverage in performance too?
Thanks
Anil
Posted by: Anil Passi | October 13, 2006 12:20 PM
Hi Anil,
Yeah with OATM you could use locally managed tablespaces which significantly improves performance.
Also you could make use of uniform extent allocation and auto allocate extent management to reduce database fragmentation.
Sam
Posted by: Sam | October 13, 2006 05:30 PM
Sam,
This is great info. I have a customer where we did implement OATM but they are not seeing performance gains due to the high number of additional files that have been created, any thought?
Thanks
Posted by: Kraig | October 20, 2006 09:35 PM
The stated advantages are those of 10g and Locally Managed Tablespaces features. I'm not aware of any advantage that is specific to having all your data and indexes it two tablespaces which is OATM as a model. In fact, some DBAs are a bit hesitant to put all of your ERP modules into a single TS. A file curruption or lose would affect the whole ERP and not just a single module.
Posted by: jim | September 18, 2007 09:08 PM