Manually trigger partition balancing

In addition to configuring a scheduled partition balancing task, you can manually trigger a partition balancing by calling the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package.

Limitations

Manual partition balancing is supported only for user tenants, not for the sys tenant.

Considerations

• If other balancing tasks are ongoing in the system, you cannot manually trigger a partition balancing task. You need to wait for the current balancing task to complete before manually triggering a partition balancing task.

  The sys tenant can check the CDB_OB_BALANCE_JOBS view to see if other balancing tasks are ongoing for a specified tenant. User tenants can check the DBA_OB_BALANCE_JOBS view to see if other balancing tasks are ongoing.

• Before manually triggering a partition balancing task, make sure that the values of the tenant-level parameters enable_rebalance and enable_transfer are both True. If either parameter is not set to True, the manual partition balancing command will fail.

• If the partitions in the current tenant are already balanced, the system will display the error message partitions are already balanced, no need to trigger partition balance.

Manually trigger a partition balance

1. Log in to the MySQL-compatible or Oracle-compatible tenant of the cluster as the tenant administrator.

   The following example shows how to connect to the database. Please adjust the connection details according to your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
   

2. Call the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package to manually trigger a partition balance.

   MySQL-compatible mode

   Oracle-compatible mode

   The statement in MySQL-compatible mode is as follows:

   CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout);
   

   The balance_timeout parameter specifies the timeout period for the background partition balancing task, in seconds. If you do not specify this parameter, the background partition balancing task (including both manually triggered and scheduled partition balancing tasks) has no timeout period and runs until completion.

   Here are some examples:

   • Trigger a partition balance without a timeout period.

     obclient [oceanbase]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
     

   • Trigger a partition balance with a timeout period of 7200 seconds.

     obclient [oceanbase]> CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200);
     

   For more information about the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package, see TRIGGER_PARTITION_BALANCE.

   The statement in Oracle-compatible mode is as follows:

   BEGIN
       DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout);
   END;
   

   The balance_timeout parameter specifies the timeout period for the background partition balancing task, in seconds. If you do not specify this parameter, the background partition balancing task (including both manually triggered and scheduled partition balancing tasks) has no timeout period and runs until completion.

   Here are some examples:

   • Trigger a partition balance without a timeout period.

     obclient [SYS]> DELIMITER //
     

     obclient [SYS]> BEGIN
         DBMS_BALANCE.TRIGGER_PARTITION_BALANCE();
     END;//
     

     obclient [SYS]> DELIMITER ;
     

   • Trigger a partition balance with a timeout period of 7200 seconds.

     obclient [SYS]> DELIMITER //
     

     obclient [SYS]> BEGIN
         DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(7200);
     END;//
     

     obclient [SYS]> DELIMITER ;
     

For more information about the TRIGGER_PARTITION_BALANCE subprogram in the DBMS_BALANCE system package, see TRIGGER_PARTITION_BALANCE.

:::

What to do next

After manually triggering partition balancing, the actual partition transfers will be executed asynchronously by the background balancing task. You can view the execution process of the partition balancing task by using the view. For more information, see View the background partition balancing task.

References

View the background partition balancing task