Search Results can_redef_table

Overview

SYS.DBMS_REDEFINITION is the Oracle-supplied PL/SQL package that implements the database's online table reorganization facility. Its business function within Oracle E-Business Suite 12.1.1 and 12.2.2 is to redefine a base table's logical and physical structure — its columns, datatypes, partitioning scheme, or storage attributes — while the table remains available for query and DML activity. In an EBS context this is most commonly applied to large transactional and interface tables whose availability windows are too narrow for an offline rebuild, allowing DBAs and application developers to convert non-partitioned tables into partitioned tables, migrate columns to new datatypes, or relocate segments to different tablespaces without a scheduled downtime event.

As recorded in the ETRM metadata, the package is owned by SYS, holds a status of VALID, and is classified under the API classification OTHER. It references the STANDARD package and defines both a specification and a body. DBMS_REDEFINITION is granted to PUBLIC, and internal dependencies exist from DBMS_REDEFINITION_INTERNAL and DBMS_DBFS_SFS, indicating its role as a foundational database service rather than an EBS application-layer API.

Key Procedures and Functions

The ETRM documentation enumerates nine callable procedures and functions. These follow the standard online redefinition lifecycle.

• CAN_REDEF_TABLE — Performs a feasibility check, confirming whether a given table can be redefined online and reporting any conditions that would prevent the operation.
• START_REDEF_TABLE — Initiates the redefinition process by creating the interim table and the materialized view infrastructure that keeps the interim copy synchronized with the original.
• SYNC_INTERIM_TABLE — Applies pending changes from the original table to the interim table so that the two are consistent at the point of cutover.
• FINISH_REDEF_TABLE — Completes the operation, swapping the original and interim tables so the redefined structure becomes the live object.
• ABORT_REDEF_TABLE — Terminates an in-progress redefinition and cleans up the interim objects without performing the swap.
• REDEF_TABLE — A single-call convenience procedure that executes the full redefinition lifecycle in one invocation, suitable for tables where no dependent objects require separate handling.
• COPY_TABLE_DEPENDENTS — Copies dependent objects such as indexes, triggers, grants, and constraints onto the interim table so they survive the swap.
• REGISTER_DEPENDENT_OBJECT — Registers a dependent object that is not automatically detected, ensuring it is migrated to the redefined table.
• UNREGISTER_DEPENDENT_OBJECT — Removes a previously registered dependent object from the redefinition scope.

Tables Accessed

DBMS_REDEFINITION does not operate against a fixed set of application tables. The ETRM metadata records no APPS-synonym table references, which is consistent with a generic database utility: the tables it reads and writes are those passed as arguments by the caller. Internally the package maintains its transient working structures — the interim table and the associated materialized view log and materialized view — plus dictionary views used to validate the source object and its dependents. The documentation further records that DBMS_REDEFINITION is referenced by two other packages, reflecting its position as a shared service relied upon by other database components.

Usage Notes

In EBS environments DBMS_REDEFINITION is normally invoked by a DBA from SQL*Plus or a database session rather than from an Oracle Forms screen or a concurrent program. Typical scenarios include converting high-volume EBS base tables to partitioned organization, changing column datatypes, and reorganizing large segments without taking the application offline. Because it is granted to PUBLIC, any schema can call it, but the executing user requires the necessary object privileges on the target table and sufficient tablespace and undo capacity for the interim copy. Redefining an EBS application-owned table carries risk for seeded objects, triggers, and indexes; the dependent-object procedures should be used so that indexes, grants, and triggers are preserved across the swap. In 12.2.2 environments, where EBS itself adopts partitioned online tables, the package remains the underlying mechanism for those operations. Custom code should prefer the explicit START/SYNC/FINISH sequence over REDEF_TABLE when control over dependent objects and cutover timing is required.