Manually trigger a partition balancing job

In addition to configuring the scheduled partition balancing job, you can also call the TRIGGER_PARTITION_BALANCE subprogram of the DBMS_BALANCE package to manually initiate a partition balancing job.

Limitations

You can manually initiate a partition balancing job only in a user tenant.

Considerations

• You can manually trigger a partition balancing job only when no ongoing partition balancing job exists in the tenant.

  You can query the CDB_OB_BALANCE_JOBS view from the sys tenant to check whether an ongoing partition balancing job exists in a specified user tenant, or query the DBA_OB_BALANCE_JOBS view in a user tenant to check whether an ongoing partition balancing job exists in the current tenant.

• Before you manually initiate a partition balancing job, make sure that the tenant-level parameters enable_rebalance and enable_transfer are both set to True. If either of the parameters is not set to True, the job will fail.

• If the partitions of the current tenant are evenly distributed, the system returns the following error message: partitions are already balanced, no need to trigger partition balance.

Manually trigger a partition balancing job

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

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

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

2. Call the TRIGGER_PARTITION_BALANCE subprogram of the DBMS_BALANCE package to manually trigger a partition balancing job.

   MySQL mode

   Oracle mode

   Execute the following statement in MySQL mode:

   CALL DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout);
   

   The balance_timeout parameter specifies the timeout period for a partition balancing job in the background, in seconds. If you leave this parameter unspecified, both automatically and manually triggered partition balancing jobs in the background will not time out and will continue until completion.

   Here are some examples:

   • Manually trigger a partition balancing job that will not time out in the background.

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

   • Manually trigger a partition balancing job and set the timeout period of the job in the background to 7,200 seconds.

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

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

   Execute the following statement in Oracle mode:

   BEGIN
       DBMS_BALANCE.TRIGGER_PARTITION_BALANCE(balance_timeout); 
   END;
   

   The balance_timeout parameter specifies the timeout period for a partition balancing job in the background, in seconds. If you leave this parameter unspecified, both automatically and manually triggered partition balancing jobs in the background will not time out and will continue until completion.

   Here are some examples:

   • Manually trigger a partition balancing job that will not time out in the background.

     obclient [SYS]> DELIMITER //
     

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

     obclient [SYS]> DELIMITER ; 
     

   • Manually trigger a partition balancing job and set the timeout period of the job in the background to 7,200 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 package, see TRIGGER_PARTITION_BALANCE.

What to do next

After you manually trigger a partition balancing job, partitions are transferred asynchronously by using balancing tasks in the background. You can query views for the execution details of the partition balancing job. For more information, see Query partition balancing jobs and tasks in the background.

References

Query partition balancing jobs and tasks in the background