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