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, users can choose to suspend the current Balance Job first. After the urgent changes are completed, the job can be resumed to continue its execution.

If the current Balance Job does not meet your requirements, you can choose to cancel the task. Additionally, if you have initiated a Transfer Partition but the task has been 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 operations performed under the system tenant.

1. Query the current running tasks using the following statement:

   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 your 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 and resume a Balance Job