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 first. After the urgent changes are completed, the job can be resumed to continue execution.

If the current Balance Job does not meet the requirements, the task can be canceled. Additionally, if you have started a Transfer Partition task but it is delayed due to an ongoing Balance Job, you can also cancel the current Balance Job.

Limitations and considerations

• 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 status of the job 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. Use the following statement to query the current running tasks.

   obclient(root@sys)[oceanbase]> 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. Choose the appropriate operation based on your business needs:

   • Suspend the ongoing Balance Job

     obclient(root@sys)[oceanbase]> ALTER SYSTEM SUSPEND BALANCE JOB TENANT = 'oracle001';
     

   • Resume the suspended Balance Job

     obclient(root@sys)[oceanbase]> ALTER SYSTEM RESUME BALANCE JOB TENANT = 'oracle001';
     

   • Cancel the Balance Job that does not meet the requirements

     obclient(root@sys)[oceanbase]> ALTER SYSTEM CANCEL BALANCE JOB TENANT = 'oracle001';
     

References

• oceanbase.CDB_OB_BALANCE_JOBS

• oceanbase.DBA_OB_TENANTS

• Cancel a Balance Job

• Suspend or resume a Balance Job