Oracle Applications tablespace model (OATM) Lesson -1

Last updated on September 17th, 2017 at 12:17 pm

OATM  stands for Oracle Applications tablespace model. As per this new model, All the tables ,view etc are catagorized in followings  types

Tablespace Type Usage
Tables that contain transaction data
Indexes on transaction tables (separated to simplify rebuilds and recovery)
Reference/setup data and indexes
Interface/temporary data and indexes
Summary Management Objects; materialized views and other data that records summary information (e.g for use in ‘daily business close’)
Other materialized views and temporary/scratchpad information
TEMP Temporary tablespace for global temporary table, sorts and hash joins
UNDO Automatic Undo Management (AUM) tablespace
SYSTEM System tablespace
Advanced Queuing/AQ (APPS_TS_QUEUE) Advanced Queuing and dependent tables and indexes.
Media(APPS_TS_MEDIA) Multimedia objects, such as text, video, sound, graphics, and spatial data.
Archive(APPS_TS_ARCHIVE) Tables that contain archived purge-related data

Tables and Indexes:
Tables are allocated to a tablespace based on their classification. Each table is classified as one of the following:
• TRANSACTION: Tables that are populated by data entry screens or batch processes and used in transactions
• SEED: Seed/static data – Static tables where little or no growth is expected
• INTERFACE: Interface/transient – transient data that may be purged or deleted
• NOLOGGING: ‘Scratchpad’ data that is created and dropped in a given workflow – no archive logging
• SUMMARY: Materialized view and other summary data for reporting; data is refreshed on a regular basis
Transaction tables are to be mapped to the TRANSACTION_TABLES tablespace, seed tables map to the APPS_TS_SEED tablespace and interface tables map to the INTERFACE tablespace.

Indexes are allocated based on the classification of the table whose columns are being indexed. For example, if the AR_ADJUSTMENTS_ALL table was classified as a transaction table it would be allocated to the TRANSACTION_TABLE tablespace and its indexes (AR_ADJUSTMENTS_U1, 2 ….) would be allocated to the TRANSACTION_INDEX tablespaces. Indexes on other types of table (currently seed and interface) are assigned to the same tablespace as the table.

The NOLOGGING tablespace is reserved for data that does not require archive logging. Transient/temporary data that does require archive logging should be placed in the INTERFACE tablespace.

Temporary Data
The TEMP tablespace is reserved for the database’s temporary data. Future versions of the database (10i) will provide additional control facilities for temporary data. We will use these to separate global temporary table data from other temporary storage such as sorts and hash joins.

Undo, System and Default Tablespaces
The UNDO tablespace is for automatic undo management. Customers may create a conventional rollback tablespace or additional undo tablespaces (e.g., for RAC installs) if they want to. The SYSTEM tablespace is reserved for the SYS and SYSTEM users. The default tablespace for all other database users will be TRANSACTION_TABLES. The temporary tablespace for each user will be TEMP. The default temporary tablespace for the database will be TEMP.

OATM(Oracle Applications tablespace model)  migration is happened through the perl utlity


Oracle Applications Tablespace Migration Utility
Main Menu
1. Migration Sizing
2. Create New Tablespaces
3. Generate Migration Commands
4. Execute Migration Commands
5. Run Migration Status Reports
6. Run Post Migration Steps
7. Run Customization Steps
8. Run Migration in Batch Mode
[Q]uit        [N]ext
Next Articles

Leave a Reply