BALANCE JOB

Purpose

This statement is used to suspend (SUSPEND), resume (RESUME), or cancel (CANCEL) a balance job.

When a tenant has an ongoing balance job and there are other urgent changes to be executed, to prevent the execution of the balance job from affecting the urgent changes, the user can choose to suspend the current balance job and resume it after the urgent changes are completed.

If the current balance job does not meet your requirements, you can cancel it. Additionally, if you have started a transfer partition task but it has been delayed due to an ongoing balance job, you can also cancel the current balance job.

Limitations and considerations

• For V4.2.5, the ability to suspend and resume balance jobs was introduced starting from V4.2.5 BP2.

• The operations to cancel, suspend, or resume a balance job can only be performed on the primary database (i.e., the primary tenant).

• When suspending or resuming a balance job, the job's status must not be CANCELING.

Privilege requirements

The current user must have the ALTER SYSTEM privilege.

Syntax

ALTER SYSTEM {SUSPEND | RESUME | CANCEL} BALANCE JOB [TENANT = 'tenant_name'];

Parameters

Examples

The following example demonstrates the operation under the system tenant.

1. Query the current tasks and their statuses.

   obclient> SELECT T.TENANT_NAME, J.TENANT_ID, J.JOB_ID, J.CREATE_TIME, J.MODIFY_TIME, J.BALANCE_STRATEGY, J.JOB_TYPE, j.STATUS
   FROM oceanbase.CDB_OB_BALANCE_JOBS J, oceanbase.DBA_OB_TENANTS T
   WHERE J.TENANT_ID = T.TENANT_ID;
   

   The result is as follows:

   +-------------+-----------+--------+----------------------------+----------------------------+---------------------------+--------------+--------+
   | TENANT_NAME | TENANT_ID | JOB_ID | CREATE_TIME                | MODIFY_TIME                | BALANCE_STRATEGY          | JOB_TYPE     | STATUS |
   +-------------+-----------+--------+----------------------------+----------------------------+---------------------------+--------------+--------+
   | oracle001   |      1004 | 300793 | 2024-02-29 16:45:12.864110 | 2024-02-29 16:45:12.864110 | LS balance by shrink      | LS_BALANCE   | DOING  |
   +-------------+-----------+--------+----------------------------+----------------------------+---------------------------+--------------+--------+
   1 row in set
   

2. Based on your business needs, choose the appropriate action:

   • Suspend the ongoing balance job

     obclient> ALTER SYSTEM SUSPEND BALANCE JOB TENANT = 'oracle001';
     

   • Resume the suspended balance job

     obclient> ALTER SYSTEM RESUME BALANCE JOB TENANT = 'oracle001';
     

   • Cancel the balance job that does not meet your requirements

     obclient> ALTER SYSTEM CANCEL BALANCE JOB TENANT = 'oracle001';
     

References

• oceanbase.CDB_OB_BALANCE_JOBS

• oceanbase.DBA_OB_TENANTS

• Cancel a balance job