DBMS_PART

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

See Also:

This chapter contains the following topics:

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.

Summary of DBMS_PART Subprograms

This table briefly describes the subprograms of DBMS_PART package.

Table 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_GIDX_JOB Procedure  
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,
   parallel          IN   VARCHAR2 DEFAULT NULL,
   options           IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 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)
parallel The parallel degree to use for the ALTER INDEX DDLs.
options <p>The following options are supported: <ul id="GUID-C6D0BD94-45B8-4371-931D-1EBF509E46B4__UL_X43_FZT_XGB" style="list-style-type: disc;"> <li>CLEANUP_ORPHANS: implies that ‘cleanup only’ mechanism is used. </li> <li>COALESCE: implies that ‘coalesce cleanup’ mechanism is used. </li> </ul> </p>

CLEANUP_GIDX_JOB Procedure

This procedure will identify and cleanup these global indexes to ensure efficiency in terms of storage and performance.

Syntax

DBMS_PART.CLEANUP_GIDX_JOB (
   parallel          IN   VARCHAR2 DEFAULT NULL,
   options           IN   VARCHAR2 DEFAULT NULL);

Parameters

Table 3 CLEANUP_GIDX_JOB Function Parameters

Parameter Description
parallel The parallel degree to use for the ALTER INDEX DDLs.
options <p>The following options are supported: <ul id="GUID-6CD02E1B-BA18-4748-8A0B-569E565644D4__UL_X43_FZT_XGB" style="list-style-type: disc;"> <li>CLEANUP_ORPHANS: implies that ‘cleanup only’ mechanism is used. </li> <li>COALESCE: implies that coalesce cleanup mechanism is used. </li> </ul> </p>

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 4 CLEANUP_ONLINE_OP Function Parameters

Parameter Description
schema_name Name of schema
table_name Name of schema
partition_name Name of partition

Usage Notes