Search Results drop_st_partition
Overview
The APPS.MSC_CL_EXCHANGE_PARTTBL package is a utility package within the Oracle Advanced Supply Chain Planning (ASCP) or "MSC" module of Oracle E-Business Suite. The "MSC" prefix indicates that it belongs to the MSC schema family, which underpins Oracle's planning products. Its name — "MSC_CL_EXCHANGE_PARTTBL" — suggests "Collection, Exchange Partition Table," pointing to the package's core function: managing partition exchange operations on staging, Operational Data Store (ODS), and related planning tables during data collection and refresh cycles.
In the ASCP architecture, large volumes of transactional data (items, BOMs, routings, demand, supply, WIP, resources) are loaded into staging tables, transformed, and then swapped into active ODS tables. The exchange-partition technique — using Oracle's ALTER TABLE ... EXCHANGE PARTITION — allows this swap to be performed as a near-instantaneous DDL operation instead of a costly row-by-row data migration. This package encapsulates that mechanism, including creation of temporary tables, partition management, and index maintenance. Status of the package is documented as VALID in ETRM 12.2.2.
Key Procedures and Functions
ETRM documents 27 procedures and functions. Key groups include:
- INITIALIZE / INITIALIZE_SWAP_TBL_LIST — Set up package state and the list of tables that will participate in the swap operation.
- CREATE_TEMP_TBL / DROP_TEMP_TBL / CREATE_TEMP_TABLE_INDEX — Manage temporary staging tables and their indexes used as exchange targets.
- EXCHANGE_PARTITION / EXCHANGE_SINGLE_TAB_PARTN / UNDO_STG_ODS_SWAP — Perform the actual partition exchange between staging and ODS structures, and provide rollback of a swap.
- CREATE_UNIQUE_INDEX / CREATE_NONUNIQUE_INDEX / ANALYSE_TEMP_TBL / GET_TABLE_INDEX / GET_SWAP_TABLE_INDEX — Index and statistics maintenance utilities that keep exchanged partitions consistent and performant.
- CREATE_PARTITION / CREATE_ST_PARTITION / DROP_ST_PARTITION / MODIFY_ST_PARTITION_ADD / MODIFY_ST_PARTITION_DROP — Interval and subpartition management for staging/ODS tables.
- LIST_CREATE_DEF_PART_STG / LIST_DROP_BAD_STAGING_PART — Housekeeping for default and obsolete staging partitions.
Tables Accessed
The package reads Oracle data dictionary and EBS metadata views to discover existing partitions, indexes, and tablespaces. Documented tables include: ALL_OBJECTS, ALL_INDEXES, ALL_IND_COLUMNS, ALL_IND_EXPRESSIONS, ALL_IND_PARTITIONS, ALL_TAB_PARTITIONS, DBA_TABLESPACES, DBA_TAB_PARTITIONS (dictionary access), and FND_APPLICATION, FND_LOOKUP_VALUES, FND_ORACLE_USERID, FND_PRODUCT_INSTALLATIONS (EBS metadata). Application-specific tables include MSC_APPS_INSTANCES, MSC_INST_PARTITIONS, and AD_DDL, which record instance configuration, partition definitions, and DDL history. It also references MSC_UTIL as a dependency and is referenced by 13 other MSC packages, including MSC_CL_BOM_ODS_LOAD, MSC_CL_ITEM_ODS_LOAD, MSC_CL_SETUP_ODS_LOAD, MSC_CL_COLLECTION, and MSC_RESOURCE_AVAILABILITY — confirming it is a shared service utility for the collection framework.
Usage Notes
This package is not user-invoked through a form. It is called internally by the MSC collection and ODS load packages during planning data refresh, typically triggered from concurrent programs such as the collections manager or data refresh routines. Because it executes DDL (EXCHANGE PARTITION, index creation), it requires elevated privileges and is sensitive to database version behavior — partition exchange semantics differ between 12.1.1 and 12.2.2. Customizations should avoid modifying it directly; extend behavior through the caller packages instead.