Transfer partitions|V4.4.2|OceanBase Database| docs|Distributed Database

When automatic load balancing cannot meet the user's specific needs for aggregating or dispersing partitions, you can manually initiate a "Transfer Partition" operation.

Limitations and considerations

The user who executes the "Transfer Partition" operation must have the ALTER SYSTEM privilege. For more information about privileges, see Overview of users and privileges.
You can execute the "Transfer Partition" operation only on the primary tenant. You can transfer only partitions under user tenants.
If the "Transfer" feature is disabled for a tenant (that is, the value of the enable_transfer parameter for the tenant is false), an error is returned when you execute the TRANSFER PARTITION statement for the tenant. If the TRANSFER PARTITION statement has been executed successfully, the corresponding "Transfer Partition" task may be canceled.
You cannot execute the "Transfer Partition" operation on system tables.
You cannot execute the "Transfer Partition" operation on user tables in the system tenant.
You cannot transfer a regular table to a broadcast log stream or a replicated table to a regular log stream.
You cannot transfer non-independent partitions. For example, partitions of a local index table and a LOB table cannot be transferred.
You cannot initiate another "Transfer Partition" operation on the same partition before the current "Transfer Partition" operation is completed.
If a balance job is being processed in the current cluster, the manually triggered "Transfer Partition" task will not be scheduled immediately. To schedule the "Transfer Partition" task as soon as possible, you can cancel the balance job. For more information about how to cancel a balance job, see Cancel a balance job.

Prerequisites

Before executing a "Transfer Partition" operation, you need to enable the "Transfer" feature. The "Transfer" feature is controlled by the tenant-level parameter enable_transfer. The default value is true, which indicates that the "Transfer" feature is enabled. For more information about the enable_transfer parameter, see enable_transfer.
Because the "Transfer Partition" strategy may conflict with the automatic rebalance strategy, we recommend that you disable the automatic rebalance strategy before you execute the "Transfer Partition" operation to ensure that the partition location remains fixed. That is, set the value of the enable_rebalance parameter for the corresponding tenant to false. For more information about the enable_rebalance parameter, see enable_rebalance.

The "Transfer Partition" operation itself is not controlled by the enable_rebalance parameter. However, when both enable_rebalance and enable_transfer are set to true, the system may automatically migrate the partition to another log stream after you migrate it to the corresponding log stream.

Transfer partition in the sys tenant

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

Here is an example of the connection command. Please replace the actual environment variables with the correct ones.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

Query the DBA_OB_TENANTS view to obtain the TENANT_ID of the target tenant.

obclient [oceanbase]> SELECT TENANT_ID FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'oracle_tenant';

The query result is as follows:

+-----------+
| TENANT_ID |
+-----------+
|      1006 |
+-----------+
1 row in set

Confirm the partition information.

You can query the CDB_OB_TABLE_LOCATIONS view to obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified partition in the table.

Obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified partition in a non-partitioned table.

Assume that a non-partitioned table named T1 exists in the oracle_tenant tenant. Here is an example of the query:

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T1' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500002 |    500002 |    200001 |  1001 |
+----------+-----------+-----------+-------+
1 row in set

Obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified partition in a partitioned table.

For a partitioned table, you only need to specify the name of the partition.

Assume that a partitioned table named TBL1_LOG_R exists in the oracle_tenant tenant.

obclient [SYS]> CREATE TABLE tbl1_log_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
 PARTITION BY RANGE(log_date) 
  (PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
   , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
   , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
   , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
   , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
   , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
   , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
   , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
   , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
   , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
   , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
   , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
   , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
   );

Here is an example of the query for the partition M202005 in the partitioned table:

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500003 |    500009 |    200006 |  1002 |
+----------+-----------+-----------+-------+
1 row in set

Obtain the TABLE_ID, OBJECT_ID, and LS_ID of the specified subpartition in a subpartitioned table.

For a subpartitioned table, you need to specify the names of both the partition and the subpartition.

Assume that a subpartitioned table named T2_F_RL exists in the oracle_tenant tenant.

obclient [SYS]> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR2(50))
 PARTITION BY RANGE(col1)
 SUBPARTITION BY LIST(col2)
 (PARTITION p0 VALUES LESS THAN(100)
   (SUBPARTITION sp0 VALUES('01'),
    SUBPARTITION sp1 VALUES('02')
    ),
  PARTITION p1 VALUES LESS THAN(200)
   (SUBPARTITION sp2 VALUES('01'),
    SUBPARTITION sp3 VALUES('02'),
    SUBPARTITION sp4 VALUES('03')
   )
  );

Here is an example of the query for the subpartition SP2 in the subpartitioned table:

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T2_F_RL' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'SP2' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500018 |    500023 |    200017 |  1003 |
+----------+-----------+-----------+-------+
1 row in set

Select a log stream as the destination for the partition transfer.

Query the CDB_OB_LS view to obtain the log stream status and information of the tenant.

obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_LS WHERE TENANT_ID = 1006;

The query result is as follows:

+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
| TENANT_ID | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG | UNIT_LIST |
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
|      1006 |     1 | NORMAL | zone1        |             0 |           0 |                NULL |     NULL | 1701244663685197789 | 1701244663685197789 |      |           |
|      1006 |  1001 | NORMAL | zone1        |             0 |        1001 | 1701239786827662637 |     NULL | 1701244663685197789 | 1701244663685197789 |      | 1001      |
|      1006 |  1002 | NORMAL | zone1        |             0 |        1002 | 1701239786831568305 |     NULL | 1701244664066906860 | 1701244664066906859 |      | 1002      |
|      1006 |  1003 | NORMAL | zone1        |             0 |        1003 | 1701239786834300282 |     NULL | 1701244664175263949 | 1701244664175263948 |      | 1003      |
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
4 rows in set

Query the CDB_OB_TABLET_TO_LS view to obtain the Tablet distribution information on the log stream.

obclient [oceanbase]> SELECT LS_ID, COUNT(*) AS C FROM oceanbase.CDB_OB_TABLET_TO_LS WHERE TENANT_ID = 1006 GROUP BY LS_ID;

The query result is as follows:

+-------+------+
| LS_ID | C    |
+-------+------+
|     1 |  578 |
|  1001 |    7 |
|  1002 |    5 |
|  1003 |    7 |
+-------+------+
4 rows in set

Based on the information displayed above, select a suitable log stream as the destination.

Execute the following command to transfer the partition.
```
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id TENANT = 'tenant_name';
```
where:
- table_id: the ID of the table.
- object_id: the unique identifier of the partition.
- ls_id: the ID of the log stream at the destination.
- tenant_name: the name of the tenant to which the partition belongs.
For example, transfer the M202005 partition of the TBL1_LOG_R table in the SYS schema of the oracle_tenant tenant from the current log stream 1002 to the log stream 1003.
```
obclient [oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003 TENANT = 'oracle_tenant';
```
After the partition transfer command is executed, you can view the task status in the following views.
1. Query the CDB_OB_TRANSFER_PARTITION_TASKS view to obtain the TASK_ID, TRANSFER_TASK_ID, and BALANCE_JOB_ID of the task.
  
  The CDB_OB_TRANSFER_PARTITION_TASKS view displays all partition transfer tasks that are being processed by all tenants. Here is an example of the query:
```
obclient [oceanbase]> SELECT TASK_ID, BALANCE_JOB_ID, TRANSFER_TASK_ID, STATUS FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASKS WHERE TENANT_ID = 1006 AND TABLE_ID = 500003 AND OBJECT_ID = 500009;
```
  The query result is as follows:
```
+---------+----------------+------------------+--------+
| TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS |
+---------+----------------+------------------+--------+
|       1 |          17304 |                1 | DOING  |
+---------+----------------+------------------+--------+
1 row in set
```
  You can check the STATUS column in the query result to confirm the task status and further view the task progress:
  - WAITING: indicates that the task is waiting and has not started yet.
  - INIT: indicates that the task has built a BALANCE_JOB. You can view the execution progress of the associated BALANCE_JOB based on the BALANCE_JOB_ID.
  - DOING: indicates that the task has started executing the transfer. You can view the associated "Transfer Partition" task based on the TRANSFER_TASK_ID. The TRANSFER_TASK_ID changes multiple times during a single "Transfer Partition" task, and a "Transfer Partition" task involves multiple transfers.
  If the query result of the CDB_OB_TRANSFER_PARTITION_TASKS view is empty, you can query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view to view the task result.
2. Query the CDB_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOB_HISTORY view based on the obtained BALANCE_JOB_ID to confirm the execution status of the associated BALANCE_JOB.
  
  The CDB_OB_BALANCE_JOBS view displays all load balancing jobs that are being executed by all tenants. Each tenant has only one load balancing job (BALANCE_JOB) at a time. Each job generates multiple load balancing tasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view displays the history of all load balancing jobs that have been executed by all tenants. Here are examples of the queries:
```
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOBS WHERE JOB_ID = 17304;
```
```
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 17304;
```
  In the queries above, replace 17304 with the BALANCE_JOB_ID obtained in the previous step.
  
  The STATUS column in the query result displays the execution status of the BALANCE_JOB:
  - DOING: indicates that the load balancing task is being executed.
  - COMPLETED: indicates that the load balancing task is completed.
  - CANCELING: indicates that the load balancing task is being canceled.
  - CANCELED: indicates that the load balancing task has been canceled.
3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view based on the obtained TRANSFER_TASK_ID to confirm the execution status of the associated TRANSFER_TASK.
  
  The CDB_OB_TRANSFER_TASKS view displays all load balancing tasks (TRANSFER_TASK) currently being executed across all tenants. At the same time, multiple load balancing tasks can be executed, and all of them belong to the same load balancing job (BALANCE_JOB). The CDB_OB_TRANSFER_TASK_HISTORY view displays the historical records of load balancing tasks executed across all tenants. Here are some query examples:
```
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASKS WHERE TASK_ID = 1;
```
```
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;
```
  In the query, 1 needs to be replaced with the TRANSFER_TASK_ID obtained in the previous step.
  
  The STATUS column in the query result displays the execution status of the TRANSFER_TASK:
  - INIT: indicates that the task is being created.
  - START: indicates that the Transfer has started.
  - DOING: indicates that the Transfer is in progress.
  - ABORTED: indicates that the Transfer task has failed and has been terminated.
  - COMPLETED: indicates that the Transfer task has been successfully completed.
  - FAILED: indicates that the Transfer task has failed.
  - CANCELED: indicates that the Transfer task has been canceled.
4. Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view using the obtained TASK_ID to confirm the result of the "Transfer Partition" task.
  
  The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the historical records of "Transfer Partition" tasks executed across all tenants.
```
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
```
  In the query, 1 needs to be replaced with the TASK_ID obtained in the previous step.
  
  The STATUS column in the query result displays the result of the "Transfer Partition" task:
  - COMPLETED: indicates that the "Transfer Partition" task has been successfully completed.
  - FAILED: indicates that the "Transfer Partition" task has failed. You can use the COMMENT column to further determine the reason for the failure. The COMMENT column typically contains the following information:
    - LS not exist or may be in DROPPING/WAIT_OFFLINE status: the log stream at the destination may not exist, or it may be in the DROPPING or WAIT_OFFLINE state.
    - LS status is not NORMAL or is in BLOCK_TABLET_IN state: the log stream at the destination may not be in the NORMAL state, for example, it may be in the CREATING or CREATED state, or it may have been BLOCK_TABLET_IN and cannot accept tablets.
    - Table has beed dropped: the table to which the partition belongs has been dropped.
    - Partition has beed dropped: the partition has been dropped.
    - Partition is already in dest LS: the partition already exists at the destination.
    - Need retry, partition may be dropped: the partition was dropped during the Transfer. The system will check whether the partition exists when the next load balancing task is generated.
    - Need retry, partition may be dropped or be transferre: the partition does not exist at the destination during the Transfer. This may be because the partition was dropped or it is no longer in the source log stream. The system will check whether the partition exists or whether a new Transfer task needs to be generated when the next load balancing task is generated.

After the "Transfer Partition" task is successfully completed, view the corresponding partition information again.

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TENANT_ID = 1006 AND DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500003 |    500009 |    200006 |  1003 |
+----------+-----------+-----------+-------+
1 row in set

From the result, you can see that the M202005 partition of the TBL1_LOG_R table in the SYS database has been migrated from the 1002 log stream to the 1003 log stream, indicating that the "Transfer Partition" operation was successful.

Transfer a partition of a user tenant

MySQL-compatible mode

Oracle-compatible mode

This topic provides operation procedures of the mysql_tenant tenant as an example.

The tenant administrator of the MySQL-compatible mode tenant connects to the database.

The connection statement example is as follows. In your actual environment, replace the parameters with those that match your environment.
```
obclient -h10.xx.xx.xx -P2883 -uroot@mysql_tenant#obdemo -p***** -A
```

Check the partition information.

Query the DBA_OB_TABLE_LOCATIONS view to obtain the TABLE_ID, OBJECT_ID, and LS_ID values of the specified partition.

Obtain the TABLE_ID, OBJECT_ID, and LS_ID values for a non-partitioned table.

Assume that the t1 table is a non-partitioned table in the mysql_tenant tenant. Query the DBA_OB_TABLE_LOCATIONS view as follows:

obclient [test]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 't1' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500002 |    500002 |    200001 |  1001 |
+----------+-----------+-----------+-------+
1 row in set

Obtain the TABLE_ID, OBJECT_ID, and LS_ID values for a partitioned table.

For a partitioned table, you only need to specify the partition name when you perform the query. The query statement example is as follows:

Assume that the tbl1_l table is a partitioned table in the mysql_tenant tenant.

obclient [test]> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
 PARTITION BY LIST(col1) 
  (PARTITION p0 VALUES IN (1, 2, 3),
   PARTITION p1 VALUES IN (5, 6),
   PARTITION p2 VALUES IN (DEFAULT)
  );

The query statement example is as follows:

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 'tbl1_l' AND PARTITION_NAME = 'p1' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500012 |    500014 |    200009 |  1001 |
+----------+-----------+-----------+-------+
1 row in set

Obtain the TABLE_ID, OBJECT_ID, and LS_ID values for a subpartitioned table.

For a subpartitioned table, you need to specify both the partition name and the subpartition name when you perform the query.

Assume that the t2_f_rclc table is a subpartitioned table in the mysql_tenant tenant.

obclient [test]> CREATE TABLE t2_f_rclc (col1 INT,col2 INT) 
 PARTITION BY RANGE COLUMNS(col1)
 SUBPARTITION BY LIST COLUMNS(col2)
 (PARTITION p0 VALUES LESS THAN(100)
   (SUBPARTITION sp0 VALUES IN(1,3),
    SUBPARTITION sp1 VALUES IN(4,6),
    SUBPARTITION sp2 VALUES IN(7,9)),
  PARTITION p1 VALUES LESS THAN(200)
   (SUBPARTITION sp3 VALUES IN(1,3),
    SUBPARTITION sp4 VALUES IN(4,6),
    SUBPARTITION sp5 VALUES IN(7,9))
 );

The query statement example is as follows:

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 't2_f_rclc' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'sp3' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500003 |    500009 |    200005 |  1002 |
+----------+-----------+-----------+-------+
1 row in set

Specify the log stream as the target endpoint.

Query the DBA_OB_LS view to obtain the log stream status and information of the tenant.

obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_LS;

The query result is as follows:

+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
| LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG | UNIT_LIST |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
|     1 | NORMAL | zone1        |             0 |           0 |                NULL |     NULL | 1701247658419109377 | 1701247658419109377 |      |           |
|  1001 | NORMAL | zone1        |             0 |        1001 | 1701239750437064613 |     NULL | 1701247658419109376 | 1701247658419109376 |      | 1001      |
|  1002 | NORMAL | zone1        |             0 |        1002 | 1701239750441114919 |     NULL | 1701247658223204599 | 1701247658223204599 |      | 1002      |
|  1003 | NORMAL | zone1        |             0 |        1003 | 1701239750443869478 |     NULL | 1701247659122731843 | 1701247659030755559 |      | 1003      |
+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
4 rows in set

Query the DBA_OB_TABLET_TO_LS view to obtain the distribution information of the tablets on the log stream.

obclient [oceanbase]> SELECT LS_ID, COUNT(*) AS C FROM oceanbase.DBA_OB_TABLET_TO_LS GROUP BY LS_ID;

The query result is as follows:

+-------+------+
| LS_ID | C    |
+-------+------+
|     1 |  590 |
|  1001 |    4 |
|  1002 |    3 |
|  1003 |    3 |
+-------+------+
4 rows in set

Based on the information displayed above, select a suitable log stream as the transfer destination.

Run the following statement to transfer a partition.
```
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id;
```
The parameters are described as follows:
- table_id: the ID of the table.
- object_id: the unique identifier of the partition.
- ls_id: the log stream ID of the target endpoint.
- tenant_name: the name of the tenant to which the partition belongs.
For example, you can run the following command to transfer the p1 partition of the tbl1_l table in the test database of the mysql_tenant tenant from the current 1001 log stream to the 1003 log stream.
```
obclient [oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500012, OBJECT_ID = 500014 TO LS 1003;
```
After you run the transfer partition statement, view the status of the task from the following views.
1. Query the DBA_OB_TRANSFER_PARTITION_TASKS view to obtain the TASK_ID, TRANSFER_TASK_ID, and BALANCE_JOB_ID values of the task.
  
  The DBA_OB_TRANSFER_PARTITION_TASKS view displays the current transfer partition tasks being handled in this tenant. The query statement example is as follows:
```
obclient [oceanbase]> SELECT TASK_ID, BALANCE_JOB_ID, TRANSFER_TASK_ID, STATUS FROM oceanbase.DBA_OB_TRANSFER_PARTITION_TASKS WHERE TABLE_ID = 500012 AND OBJECT_ID = 500014;
```
  The query result is as follows:
```
+---------+----------------+------------------+--------+
| TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS |
+---------+----------------+------------------+--------+
|       1 |          26506 |                1 | DOING  |
+---------+----------------+------------------+--------+
1 row in set
```
  You can query the STATUS column of the query result to confirm the task status and then view the progress of the task.
  - WAITING: Indicates that the task is waiting for scheduling to start.
  - INIT: Indicates that the BALANCE_JOB has been created. You can view the progress of the associated BALANCE_JOB based on the BALANCE_JOB_ID.
  - DOING: Indicates that the task has started transferring data. You can view the progress of the associated transfer partition task based on the TRANSFER_TASK_ID. The TRANSFER_TASK_ID changes multiple times during a single transfer partition task, which involves multiple data transfer operations.
  If the DBA_OB_TRANSFER_PARTITION_TASKS view returns an empty result, you can query the DBA_OB_TRANSFER_PARTITION_TASK_HISTORY view to view the task results.
2. Based on the BALANCE_JOB_ID value obtained from the previous query, query the DBA_OB_BALANCE_JOBS view or the DBA_OB_BALANCE_JOB_HISTORY view to view the execution status of the associated BALANCE_JOB.
  
  The DBA_OB_BALANCE_JOBS view displays the current load balancing job of this tenant. Each tenant has only one load balancing job (BALANCE_JOB) at a time. Each job generates multiple load balancing tasks (TRANSFER_TASK). The DBA_OB_BALANCE_JOB_HISTORY view displays the completed load balancing jobs of this tenant. The query statements are as follows:
```
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOBS WHERE JOB_ID = 26506;
```
```
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 26506;
```
  In the preceding statements, 26506 is to be replaced with the BALANCE_JOB_ID obtained from the previous step.
  
  The STATUS column in the query result displays the execution status of the BALANCE_JOB:
  - DOING: Indicates that the load balancing job is in progress.
  - COMPLETED: Indicates that the load balancing job is successful.
  - CANCELING: Indicates that the load balancing job is being canceled.
  - CANCELED: Indicates that the load balancing job is canceled.
3. Based on the TRANSFER_TASK_ID value obtained from the previous query, query the DBA_OB_TRANSFER_TASKS view or the DBA_OB_TRANSFER_TASK_HISTORY view to view the execution status of the associated TRANSFER_TASK.
  
  The DBA_OB_TRANSFER_TASKS view displays the current load balancing tasks of this tenant. Multiple load balancing tasks (TRANSFER_TASK) may be executed at the same time. These tasks belong to the same load balancing job (BALANCE_JOB). The DBA_OB_TRANSFER_TASK_HISTORY view displays the completed load balancing tasks of this tenant. The query statements are as follows:
```
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASKS WHERE TASK_ID = 1;
```
```
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;
```
  Replace 1 with the TRANSFER_TASK_ID obtained in the preceding step.
  
  The STATUS column in the query result indicates the execution status of the TRANSFER_TASK:
  - INIT: indicates that the task is being created.
  - START: indicates that the transfer task has started.
  - DOING: indicates that the transfer task is in progress.
  - ABORTED: indicates that the transfer task failed and was terminated.
  - COMPLETED: indicates that the transfer task was executed successfully.
  - FAILED: indicates that the transfer task failed.
  - CANCELED: indicates that the transfer task was canceled.
4. Query the DBA_OB_TRANSFER_PARTITION_TASK_HISTORY view based on the obtained TASK_ID to confirm the result of the "Transfer Partition" task.
  
  The DBA_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the history of executed "Transfer Partition" tasks in the current tenant. Here is an example of a query:
```
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
```
  Replace 1 with the TASK_ID obtained in the preceding step.
  
  The STATUS column in the query result indicates the result of the "Transfer Partition" task:
  - COMPLETED: indicates that the "Transfer Partition" task was executed successfully.
  - FAILED: indicates that the "Transfer Partition" task failed. You can check the COMMENT column to determine the cause of the failure. The COMMENT column contains the following common information:
    - LS not exist or may be in DROPPING/WAIT_OFFLINE status: the log stream on the destination may not exist, or it may be in the DROPPING or WAITOFFLINE state.
    - LS status is not NORMAL or is in BLOCK_TABLET_IN state: the log stream on the destination may not be in the NORMAL state. For example, it may be in the CREATING or CREATED state, or it may be in the BLOCK_TABLET_IN state and cannot receive tablets.
    - Table has beed dropped: the table to which the partition belongs was dropped.
    - Partition has beed dropped: the partition was dropped.
    - Partition is already in dest LS: the partition already exists on the destination.
    - Need retry, partition may be dropped: the partition was dropped during the Transfer. The system will check whether the partition exists when the next load balancing task is generated.
    - Need retry, partition may be dropped or be transferre: the partition does not exist on the destination during the Transfer. This may be because the partition was dropped or it is no longer on the source log stream. The system will check whether the partition exists or whether a new Transfer task needs to be generated when the next load balancing task is generated.

After the "Transfer Partition" task is executed successfully, view the partition information again.

obclient [oceanbase]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM oceanbase.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'test' AND TABLE_NAME= 'tbl1_l' AND PARTITION_NAME = 'p1' LIMIT 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500012 |    500014 |    200009 |  1003 |
+----------+-----------+-----------+-------+
1 row in set

The result indicates that the p1 partition of the tbl1_l table in the test database was migrated from log stream 1001 to log stream 1003, and the "Transfer Partition" operation was successful.

This topic takes the oracle_tenant tenant as an example to describe the procedure.

Connect to the database as the tenant administrator of the Oracle-compatible mode tenant.

Here is an example of connecting to the database. Please use the actual environment.
```
obclient -h10.xx.xx.xx -P2883 -usys@oracle_tenant#obdemo -p***** -A
```

View the partition information.

Query the DBA_OB_TABLE_LOCATIONS view to obtain the TABLET_ID, OBJECT_ID, and LS_ID of the specified partition.

Obtain the TABLET_ID, OBJECT_ID, and LS_ID of a non-partitioned table

Assume that the T1 table exists in the oracle_tenant tenant. Here is an example of querying the TABLET_ID, OBJECT_ID, and LS_ID of the T1 table:

obclient [SYS]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T1' AND ROWNUM = 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500002 |    500002 |    200001 |  1001 |
+----------+-----------+-----------+-------+
1 row in set

Obtain the TABLET_ID, OBJECT_ID, and LS_ID of a partitioned table

For a partitioned table, you only need to specify the partition name. Here is an example:

Assume that the TBL1_LOG_R table exists in the oracle_tenant tenant.

obclient [SYS]> CREATE TABLE tbl1_log_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
 PARTITION BY RANGE(log_date) 
  (PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
   , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
   , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
   , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
   , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
   , PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
   , PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
   , PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
   , PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
   , PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
   , PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
   , PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
   , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
   );

Here is an example of querying the M202005 partition of the TBL1_LOG_R table:

obclient [SYS]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'SYS' AND TABLE_NAME= 'TBL1_LOG_R' AND PARTITION_NAME = 'M202005' AND ROWNUM = 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500003 |    500009 |    200006 |  1002 |
+----------+-----------+-----------+-------+
1 row in set

Obtain the TABLET_ID, OBJECT_ID, and LS_ID of a subpartitioned table

For a subpartitioned table, you need to specify both the partition name and the subpartition name.

Assume that the T2_F_RL table exists in the oracle_tenant tenant.

obclient [SYS]> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR2(50))
 PARTITION BY RANGE(col1)
 SUBPARTITION BY LIST(col2)
 (PARTITION p0 VALUES LESS THAN(100)
   (SUBPARTITION sp0 VALUES('01'),
    SUBPARTITION sp1 VALUES('02')
    ),
  PARTITION p1 VALUES LESS THAN(200)
   (SUBPARTITION sp2 VALUES('01'),
    SUBPARTITION sp3 VALUES('02'),
    SUBPARTITION sp4 VALUES('03')
   )
  );

Here is an example of querying the SP2 subpartition of the T2_F_RL table:

obclient [SYS]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T2_F_RL' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'SP2' AND ROWNUM = 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500018 |    500023 |    200017 |  1003 |
+----------+-----------+-----------+-------+
1 row in set

Select the destination log stream.

Query the DBA_OB_LS view to obtain the status and information of the log streams of the tenant.

obclient [SYS]> SELECT * FROM SYS.DBA_OB_LS;

The query result is as follows:

+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
| TENANT_ID | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG | UNIT_LIST |
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
|      1006 |     1 | NORMAL | zone1        |             0 |           0 |                NULL |     NULL | 1701244663685197789 | 1701244663685197789 |      |           |
|      1006 |  1001 | NORMAL | zone1        |             0 |        1001 | 1701239786827662637 |     NULL | 1701244663685197789 | 1701244663685197789 |      | 1001      |
|      1006 |  1002 | NORMAL | zone1        |             0 |        1002 | 1701239786831568305 |     NULL | 1701244664066906860 | 1701244664066906859 |      | 1002      |
|      1006 |  1003 | NORMAL | zone1        |             0 |        1003 | 1701239786834300282 |     NULL | 1701244664175263949 | 1701244664175263948 |      | 1003      |
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+-----------+
4 rows in set

Query the DBA_OB_TABLET_TO_LS view to obtain the distribution of tablets on the log stream.

obclient [SYS]> SELECT LS_ID, COUNT(*) AS C FROM SYS.DBA_OB_TABLET_TO_LS GROUP BY LS_ID;

The query result is as follows:

+-------+------+
| LS_ID | C    |
+-------+------+
|     1 |  578 |
|  1001 |    7 |
|  1002 |    5 |
|  1003 |    7 |
+-------+------+
4 rows in set

Based on the information displayed above, select a suitable log stream as the destination.

Execute the following command to perform the "Transfer Partition" operation.
```
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id;
```
where:
- table_id: the ID of the table.
- object_id: the unique identifier of the partition.
- ls_id: the ID of the log stream on the destination.
- tenant_name: the tenant to which the partition to be transferred belongs.
The following example shows how to transfer the M202005 partition of the TBL1_LOG_R table in the SYS database of the oracle_tenant tenant from log stream 1002 to log stream 1003.
```
obclient [SYS]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003;
```
After the transfer partition command is executed successfully, you can view the task status in the following views.
1. Query the DBA_OB_TRANSFER_PARTITION_TASKS view to obtain the TASK_ID, TRANSFER_TASK_ID, and BALANCE_JOB_ID of the task.
  
  The DBA_OB_TRANSFER_PARTITION_TASKS view displays the transfer partition tasks being processed in the current tenant. The query example is as follows:
```
obclient [SYS]> SELECT TASK_ID, BALANCE_JOB_ID, TRANSFER_TASK_ID, STATUS FROM SYS.DBA_OB_TRANSFER_PARTITION_TASKS WHERE TABLE_ID = 500003 AND OBJECT_ID = 500009;
```
  The query result is as follows:
```
+---------+----------------+------------------+--------+
| TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS |
+---------+----------------+------------------+--------+
|       1 |          17304 |                1 | DOING  |
+---------+----------------+------------------+--------+
1 row in set
```
  You can view the task status based on the value of the STATUS column in the query result. The task execution progress is as follows:
  - WAITING: indicates that the task is waiting to be scheduled.
  - INIT: indicates that the task has been associated with a BALANCE_JOB. You can view the execution progress of the associated BALANCE_JOB based on the value of the BALANCE_JOB_ID column.
  - DOING: indicates that the task is being executed. You can view the execution progress of the associated transfer partition task based on the value of the TRANSFER_TASK_ID column. The value of the TRANSFER_TASK_ID column changes frequently. A transfer partition task may involve multiple transfers.
  If the query result of the DBA_OB_TRANSFER_PARTITION_TASKS view is empty, you can query the DBA_OB_TRANSFER_PARTITION_TASK_HISTORY view to view the task result.
2. Query the DBA_OB_BALANCE_JOBS or DBA_OB_BALANCE_JOB_HISTORY view based on the obtained BALANCE_JOB_ID to view the execution status of the associated BALANCE_JOB.
  
  The DBA_OB_BALANCE_JOBS view displays the load balancing tasks being executed in the current tenant. Only one load balancing task (BALANCE_JOB) can be executed in a tenant at a time. Each load balancing task generates multiple transfer tasks (TRANSFER_TASK). The DBA_OB_BALANCE_JOB_HISTORY view displays the load balancing tasks executed in the current tenant. The query examples are as follows:
```
obclient [SYS]> SELECT * FROM SYS.DBA_OB_BALANCE_JOBS WHERE JOB_ID = 17304;
```
```
obclient [SYS]> SELECT * FROM SYS.DBA_OB_BALANCE_JOB_HISTORY WHERE JOB_ID = 17304;
```
  In the preceding examples, 17304 is the value of the BALANCE_JOB_ID obtained in the previous step.
  
  The STATUS column in the query result displays the execution status of the BALANCE_JOB:
  - DOING: indicates that the load balancing task is being executed.
  - COMPLETED: indicates that the load balancing task is executed successfully.
  - CANCELING: indicates that the load balancing task is being canceled.
  - CANCELED: indicates that the load balancing task is canceled.
3. Query the DBA_OB_TRANSFER_TASKS or DBA_OB_TRANSFER_TASK_HISTORY view based on the obtained TRANSFER_TASK_ID to view the execution status of the associated TRANSFER_TASK.
  
  The DBA_OB_TRANSFER_TASKS view displays the transfer tasks being executed in the current tenant. Multiple transfer tasks (TRANSFER_TASK) can be executed in a tenant at a time. These transfer tasks belong to the same load balancing task (BALANCE_JOB). The DBA_OB_TRANSFER_TASK_HISTORY view displays the transfer tasks executed in the current tenant. The query examples are as follows:
```
obclient [SYS]> SELECT * FROM SYS.DBA_OB_TRANSFER_TASKS WHERE TASK_ID = 1;
```
```
obclient [SYS]> SELECT * FROM SYS.DBA_OB_TRANSFER_TASK_HISTORY WHERE TASK_ID = 1;
```
  In the preceding examples, 1 is the value of the TRANSFER_TASK_ID obtained in the previous step.
  
  The STATUS column in the query result displays the execution status of the TRANSFER_TASK:
  - INIT: indicates that the task is being created.
  - START: indicates that the task has started.
  - DOING: indicates that the task is being executed.
  - ABORTED: indicates that the transfer task is aborted.
  - COMPLETED: indicates that the transfer task is executed successfully.
  - FAILED: indicates that the transfer task is failed.
  - CANCELED: indicates that the transfer task is canceled.
4. Query the DBA_OB_TRANSFER_PARTITION_TASK_HISTORY view based on the obtained TASK_ID to view the result of the transfer partition task.
  
  The DBA_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the transfer partition tasks executed in the current tenant. The query example is as follows:
```
obclient [SYS]> SELECT * FROM SYS.DBA_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
```
  In the preceding example, 1 is the value of the TASK_ID obtained in the previous step.
  
  The STATUS column in the query result displays the result of the transfer partition task:
  - COMPLETED: indicates that the transfer partition task is executed successfully.
  - FAILED: indicates that the transfer partition task is failed. You can view the reason for the task failure in the COMMENT column. The following are some common messages in the COMMENT column:
    - LS not exist or may be in DROPPING/WAIT_OFFLINE status: indicates that the log stream at the destination may not exist, or is in the DROPPING or WAITOFFLINE state.
    - LS status is not NORMAL or is in BLOCK_TABLET_IN state: indicates that the log stream at the destination is not in the NORMAL state. For example, the log stream is in the CREATING or CREATED state, or is blocked by the BLOCK_TABLET_IN state.
    - Table has beed dropped: indicates that the table to which the partition to be transferred belongs has been dropped.
    - Partition has beed dropped: indicates that the partition to be transferred has been dropped.
    - Partition is already in dest LS: indicates that the partition already exists at the destination.
    - Need retry, partition may be dropped: indicates that the partition was dropped during the transfer. The system will check whether the partition exists when the next load balancing task is generated.
    - Need retry, partition may be dropped or be transferre: indicates that the partition does not exist at the destination. The partition may have been dropped or may not exist at the source log stream. The system will check whether the partition exists or whether a new transfer task needs to be generated when the next load balancing task is generated.

After the "Transfer Partition" task is executed, check the partition information again.

obclient [SYS]> SELECT TABLE_ID AS TABLE_ID, OBJECT_ID, TABLET_ID, LS_ID FROM SYS.DBA_OB_TABLE_LOCATIONS WHERE DATABASE_NAME = 'SYS' AND TABLE_NAME= 'T2_F_RL' AND PARTITION_NAME = 'P1' AND SUBPARTITION_NAME = 'SP2' AND ROWNUM = 1;

The query result is as follows:

+----------+-----------+-----------+-------+
| TABLE_ID | OBJECT_ID | TABLET_ID | LS_ID |
+----------+-----------+-----------+-------+
|   500003 |    500009 |    200006 |  1003 |
+----------+-----------+-----------+-------+
1 row in set

The result indicates that the M202005 partition of the TBL1_LOG_R table in the SYS database has been successfully migrated from log stream 1002 to log stream 1003.

OceanBase

Customer Stories

Documentation

Transfer partitions

Limitations and considerations

Prerequisites

Transfer partition in the sys tenant

Transfer a partition of a user tenant

References