Search Results chk_new_tables




Overview
Explore in AI Chat

Overview

FND_TS_MIG_UTIL is an Oracle E-Business Suite utilities package (API classification: UTIL) owned by the APPS schema. Its name — "Tablespace Migration Utility" — reflects its central purpose: supporting the migration of database tablespaces and the management of storage preferences across the EBS schema. The package was authored with an AUTHID DEFINER clause, meaning its procedures execute with the privileges of the defining schema (APPS) rather than the invoker, which is appropriate for a low-level administrative utility that must operate on data dictionary views and storage configuration tables.

The package addresses the transition from dictionary-managed to locally managed tablespaces, a significant concern during the lifecycle of older EBS implementations. It also centralizes the mapping of logical tablespace types — such as transaction tables, AQ (Advanced Queuing) tables, media, transaction indexes, summary (materialized view) storage, and unclassified objects — to concrete tablespace names. Package-level constants including l_def_tab_tsp, l_aq_tab_tsp, l_md_tab_tsp, l_def_ind_tsp, l_def_mv_tsp, l_unclass_tsp, and l_unclass_ind_tsp define these default mappings. The source header identifies the file as fndptmus.pls at version 120.2.

Key Procedures and Functions

  • get_db_version — Function returning a NUMBER, used to determine the database version so the utility can branch its behavior for different Oracle releases.
  • upd_gl_storage_param — Procedure that sets the TABLESPACE_NAME in GL_STORAGE_PARAMETERS to INTERFACE, aligning General Ledger interface storage with migration targets.
  • migrate_tsp_to_local — Procedure that performs the migration of tablespaces to locally managed status.
  • get_tablespace_name — Function accepting a tablespace type and returning the VARCHAR2 tablespace name mapped to that logical type. This is the function targeted by the user's search.
  • get_tablespace_ues — Function accepting a tablespace name and returning the allocated usage in bytes as a NUMBER.
  • chk_new_tablespaces — Procedure that validates newly created tablespaces.
  • chk_new_tables — Procedure that checks new tables for correct tablespace placement.
  • chk_product_defaults — Procedure that verifies product-level default storage settings.
  • crt_storage_pref — Procedure that creates a storage preference for a given tablespace type and preference name.
  • upd_fot_username — Procedure that updates the FND_ORACLE_USERID (FOT) username association.
  • process_rules — Procedure that applies migration rules for a given APPS schema name.
  • set_defaults — Procedure that establishes default tablespace assignments.
  • crt_txn_ind_pref — Procedure that creates the transaction index storage preference.

Tables Accessed

The package reads and writes both EBS application tables (via APPS synonyms) and Oracle data dictionary views. Configuration and rule tables include FND_TABLESPACES, FND_TS_MIG_RULES, FND_OBJECT_TABLESPACES, FND_ORACLE_USERID, GL_STORAGE_PARAMETERS, and CTX_USER_PREFERENCES. Product context is obtained from FND_PRODUCT_GROUPS and FND_PRODUCT_INSTALLATIONS. Dictionary views such as DBA_TABLES, DBA_TABLESPACES, DBA_TS_QUOTAS, DBA_SNAPSHOTS, DBA_SNAPSHOT_LOGS, and DBA_QUEUE_TABLES supply metadata on existing segments and their placement, while CTX_DDL supports Oracle Text index operations. These accesses explain the utility's need for AUTHID DEFINER privileges.

Usage Notes

FND_TS_MIG_UTIL is an internal administrative utility rather than an end-user API. It is referenced by two other EBS packages and is typically invoked during tablespace migration or storage-configuration projects, often through concurrent programs or DBA-driven scripts executed during maintenance windows. Custom code should not call it for routine development; the get_tablespace_name function is the most commonly queried entry point because it resolves a logical tablespace type to its configured physical name.