The DBMS_PART package provides an interface for maintenance and management operations on partitioned objects.

See Also:

This chapter contains the following topics:

113.1 DBMS_PART Security Model

DBMS_PART is an invoker's rights package, running with the privileges of the user.

113.2 DBMS_PART Operational Notes

The following operational notes apply to DBMS_PART.

  • DBMS_PART ignores all the errors that it runs into during the cleanup process.

  • To display the message PL/SQL procedure executed successfully requires at least one cleanup operation to be successful.

113.3 Summary of DBMS_PART Subprograms

This table briefly describes the subprograms of DBMS_PART package.

Table 113-1 DBMS_PART Package Subprograms

Subprogram Description


Gathers the list of global indexes where optimized asynchronous index maintenance has taken place to clean up entries pointing to data segments that no longer exist


Cleans up failed online move operations

113.3.1 CLEANUP_GIDX Procedure

As a consequence of prior partition maintenance operations with asynchronous global index maintenance, global indexes can contain entries pointing to data segments that no longer exist. These stale index rows will not cause any correctness issues or corruptions during any operation on the table or index, whether these are queries, DMLs, DDLs or analyze. This procedure will identify and cleanup these global indexes to ensure efficiency in terms of storage and performance.


   schema_name_in    IN   VARCHAR2 DEFAULT NULL,
   table_name_in     IN   VARCHAR2 DEFAULT NULL);


Table 113-2 CLEANUP_GIDX Function Parameters

Parameter Description


Non-NULL processes only indexes on tables in the given schema


Non-NULL processes only indexes on the given table in the given schema (schema_name_in must be non-NULL if table_name_in is non-NULL)

113.3.2 CLEANUP_ONLINE_OP Procedure

There are many possible points of failure when performing ALTER TABLE ... MOVE PARTITION ... ONLINE operations. This procedure pro-actively cleans up such failed online move operations instead of waiting for the background process (SMON) to do so.


   schema_name       IN   VARCHAR2 DEFAULT NULL,
   table_name        IN   VARCHAR2 DEFAULT NULL, 
   partition_name    IN   VARCHAR2 DEFAULT NULL);


Table 113-3 CLEANUP_ONLINE_OP Function Parameters

Parameter Description


Name of schema


Name of schema


Name of partition

Usage Notes

  • If schema_name, table_name and partition_name are specified, this cleans up the failed online move operation for the specified partition.

  • If schema_name and table_name are specified, this cleans up all failed online move operations for all the partitions of the specified table.

  • If only schema_name is specified, this cleans up all failed online move operations in the schema.

  • If no arguments are provided, we cleans up all the failed online move operations in the system.

  • All other cases raise ORA-20000 to inform the user of invalid inputs as arguments.