Cancel a "Transfer Partition" task

After you manually initiate a "Transfer Partition" task, you can cancel the task based on your business needs.

Limitations

• You cannot cancel a "Transfer Partition" task in the DOING state. You can cancel a "Transfer Partition" task in other states.

• You can cancel a "Transfer Partition" task only on the primary database (that is, the primary tenant).

Cancel a "Transfer Partition" task in the sys tenant

1. Log in to the sys tenant of the cluster as the root user.

   The following example shows how to connect to the database. Replace the actual environment parameters as needed.

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

2. Query the CDB_OB_TRANSFER_PARTITION_TASKS view to obtain the status of the current task.

   obclient [oceanbase]> SELECT TENANT_ID, TABLE_ID, OBJECT_ID, STATUS FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASKS WHERE TENANT_ID = 1002;
   

   The query result is as follows:

   +-----------+----------+-----------+---------+
   | TENANT_ID | TABLE_ID | OBJECT_ID | STATUS  |
   +-----------+----------+-----------+---------+
   |      1002 |   500006 |    500008 | WAITING |
   +-----------+----------+-----------+---------+
   1 row in set
   

   Only tasks with a status other than DOING (such as WAITING or INIT) can be canceled.

   If the task you want to cancel has a status of DOING, you can disable the Transfer feature by setting the tenant-level parameter enable_transfer to false. After disabling Transfer, all DOING status tasks will revert to the WAITING state, allowing you to cancel the "Transfer Partition" task.

   For more information about the enable_transfer parameter, see enable_transfer.

3. Based on the query result, choose the appropriate operation:

   • Cancel a "Transfer Partition" task for a specified tenant and partition

     ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID [=] table_id OBJECT_ID [=] object_id TENANT = 'tenant_name';
     

     Where:

     • table_id: the ID of the table from which the partition is to be canceled.

     • object_id: the ID of the partition to be canceled.

     • tenant_name: the name of the tenant from which the partition is to be canceled.

     Example:

     obclient [oceanbase]> ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID = 500006, OBJECT_ID = 500008 TENANT = 'mysql_tenant';
     

   • Cancel all "Transfer Partition" tasks for a specified tenant

     ALTER SYSTEM CANCEL TRANSFER PARTITION ALL TENANT = 'tenant_name';
     

     Example:

     obclient [oceanbase]> ALTER SYSTEM CANCEL TRANSFER PARTITION ALL TENANT = 'mysql_tenant';
     

Cancel a "Transfer Partition" task in the user tenant

1. A tenant administrator of a MySQL-compatible mode or Oracle-compatible mode tenant connects to the database.

   The following example shows how to connect to the database. Replace the values in the example with those in your environment.

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

2. Query the DBA_OB_TRANSFER_PARTITION_TASKS view to obtain the status of the current task.

   MySQL-compatible mode

   Oracle-compatible mode

   The query statement in MySQL-compatible mode is as follows:

   obclient [oceanbase]> SELECT TABLE_ID, OBJECT_ID, STATUS FROM oceanbase.DBA_OB_TRANSFER_PARTITION_TASKS;
   

   The query statement in Oracle-compatible mode is as follows:

   obclient [SYS]> SELECT TABLE_ID, OBJECT_ID, STATUS FROM SYS.DBA_OB_TRANSFER_PARTITION_TASKS;
   

   The query result is as follows:

   +----------+-----------+--------+
   | TABLE_ID | OBJECT_ID | STATUS |
   +----------+-----------+--------+
   |   500046 |    500052 | INIT   |
   +----------+-----------+--------+
   1 row in set
   

   Only tasks with a status other than DOING (such as WAITING or INIT) can be canceled.

   If the task you want to cancel has a status of DOING, you can disable the Transfer feature by setting the tenant-level parameter enable_transfer to false. After disabling the Transfer feature, all DOING status tasks will revert to the WAITING state, and you can then cancel the "Transfer Partition" task.

   For more information about the enable_transfer parameter, see enable_transfer.

3. Based on the query result, choose an appropriate operation:

   • Cancel a "Transfer Partition" task for a specified partition in the current tenant

     ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID [=] table_id OBJECT_ID [=] object_id;
     

     Where:

     • table_id: the table ID of the partition to be canceled.

     • object_id: the partition to be canceled.

     Example:

     obclient [oceanbase]> ALTER SYSTEM CANCEL TRANSFER PARTITION TABLE_ID = 500046, OBJECT_ID = 500052;
     

   • Cancel all "Transfer Partition" tasks in the current tenant

     obclient> ALTER SYSTEM CANCEL TRANSFER PARTITION ALL;
     

References

• Transfer partitions

• Cancel a load balancing Job