113 DBMS_PART

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

DBMS_PART Security Model

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

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.

Summary of DBMS_PART Subprograms

This table briefly describes the subprograms of DBMS_PART package.

Table 113-1 DBMS_PART Package Subprograms

Subprogram	Description
CLEANUP_GIDX Procedure	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
CLEANUP_ONLINE_OP Procedure	Cleans up failed online move operations

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.

Syntax

DBMS_PART.CLEANUP_GIDX (
   schema_name_in    IN   VARCHAR2 DEFAULT NULL,
   table_name_in     IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 113-2 CLEANUP_GIDX Function Parameters

Parameter	Description
`schema_name_in`	Non-`NULL` processes only indexes on tables in the given schema
`table_name_in`	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`)

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.

Syntax

DBMS_PART.CLEANUP_ONLINE_OP (
   schema_name       IN   VARCHAR2 DEFAULT NULL,
   table_name        IN   VARCHAR2 DEFAULT NULL, 
   partition_name    IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 113-3 CLEANUP_ONLINE_OP Function Parameters

Parameter	Description
`schema_name`	Name of schema
`table_name`	Name of schema
`partition_name`	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.