CLEAR_BALANCE_WEIGHT

The CLEAR_BALANCE_WEIGHT procedure is used to clear partition weights.

Limitations and considerations

• Only user tenants can clear partition weights by calling the CLEAR_BALANCE_WEIGHT procedure. System tenants do not support clearing partition weights.

• Clearing table-level partition weights removes all table-level weights set on the partitions. If partition-level weights were set for the table, they will be retained.

Syntax

DBMS_BALANCE.CLEAR_BALANCE_WEIGHT(
    schema_name         IN VARCHAR2,
    table_name          IN VARCHAR2,
    partition_name      IN VARCHAR2 DEFAULT NULL,
    subpartition_name   IN VARCHAR2 DEFAULT NULL);

Parameters

Examples

1. Assume that in the SYS user (in Oracle mode, the schema name is the same as the username), there is a table TBL1_H with partition weights set.

   To query the partition weight information, use the following statement:

   obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The query result is as follows:

   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   | TABLE_ID | PARTITION_ID | SUBPARTITION_ID | WEIGHT | DATABASE_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | TABLEGROUP_NAME | DATABASE_ID | TABLEGROUP_ID | OBJECT_ID |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   |   500002 |         NULL |            NULL |      1 | SYS           | TBL1_H     | NULL           | NULL              | NULL            |      201006 |          NULL |    500002 |
   |   500002 |       500003 |            NULL |      2 | SYS           | TBL1_H     | P0             | NULL              | NULL            |      201006 |          NULL |    500003 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   2 rows in set
   

2. Clear the table-level partition weights for the table TBL1_H.

   obclient(SYS@oracle001)[SYS]> delimiter //
   

   oobclient(SYS@oracle001)[SYS]> BEGIN 
             DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('SYS','TBL1_H');
             END;//
   

   obclient(SYS@oracle001)[SYS]> delimiter ;
   

3. Query the partition weight information set for the table after clearing.

   obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The result is as follows:

   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   | TABLE_ID | PARTITION_ID | SUBPARTITION_ID | WEIGHT | DATABASE_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | TABLEGROUP_NAME | DATABASE_ID | TABLEGROUP_ID | OBJECT_ID |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   |   500002 |       500003 |            NULL |      2 | SYS          | TBL1_H     | P0             | NULL              | NULL            |      201006 |          NULL |    500003 |
   +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+
   1 row in set
   

4. Clear the partition weights for the primary partition P0 in the table TBL1_H.

   obclient(SYS@oracle001)[SYS]> delimiter //
   

   obclient(SYS@oracle001)[SYS]> BEGIN 
           DBMS_BALANCE.CLEAR_BALANCE_WEIGHT('SYS','TBL1_H','P0');
           END;//
   

   obclient(SYS@oracle001)[SYS]> delimiter ;
   

5. Query the partition weight information set for the table again after clearing.

   obclient(SYS@oracle001)[SYS]> SELECT * FROM SYS.DBA_OB_OBJECT_BALANCE_WEIGHT;
   

   The query result is empty.

References

• Intra-tenant balancing

• Set the balance weight for a table

• Clear the balance weight