TRANSFER PARTITION|V4.3.1| docs|Distributed Database

TRANSFER PARTITION

Last Updated：2024-06-28 05:30:31 Updated

Purpose

You can use this statement to transfer a partition to the specified log stream.

Required privileges

You must have the ALTER SYSTEM privilege to execute the ALTER SYSTEM TRANSFER PARTITION statement. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode and Privilege types in Oracle mode.

Syntax

ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] $table_id, OBJECT_ID [=] $object_id TO LS $ls_id [tenant = '$tenant_name']

Limitations and considerations

You can perform the TRANSFER PARTITION operation only in the primary tenant to transfer only a partition of a user tenant.
After you set the enable_transfer parameter to false to disable the transfer feature for a tenant, an error will be returned if you execute the TRANSFER PARTITION statement for this tenant, and a partition transfer task initiated by a successfully executed TRANSFER PARTITION statement may be canceled.
You cannot perform partition transfer for a system table.
You cannot perform partition transfer for a user table in the sys tenant.
You cannot transfer a normal table to a broadcast log stream or transfer a replicated table to a normal log stream.
You cannot transfer a non-independent partition, such as a partition of a local index table or large object (LOB) table.
If a transfer operation is in progress for a partition, you cannot initiate another transfer operation for this partition before the current transfer operation is completed.
If a load balancing job (BALANCE_JOB) is in progress in the current cluster, a manually initiated partition transfer task will not be immediately scheduled.

Parameters

Parameter	Description
$table_id	The ID of the partitioned table to which the partition to be transferred belongs.
$object_id	The unique ID of the partition to be transferred. The value can be an integer.
$ls_id	The ID of the destination log stream to which the partition is to be transferred.
$tenant_name	The name of the tenant. This parameter is required when you execute this statement in the sys tenant.

Examples

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

Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.
```
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
```

Query the DBA_OB_TENANTS view for the 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 for TABLET_ID, OBJECT_ID, and LS_ID values of the specified partition.

Query TABLET_ID, OBJECT_ID, and LS_ID values of a non-partitioned table

Assume that non-partitioned table T1 exists in the oracle_tenant tenant. A sample query is as follows:

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

Query TABLET_ID, OBJECT_ID, and LS_ID values of a partitioned table

You need to specify only the partition name for the query.

Assume that partitioned table TBL1_LOG_R exists in the oracle_tenant tenant. A sample query is as follows:

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)
   );

The following sample code queries related information about the M202005 partition 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

Query TABLET_ID, OBJECT_ID, and LS_ID values of a subpartitioned table

You need to specify both the partition and subpartition names for the query.

Assume that subpartitioned table T2_F_RL exists in the oracle_tenant tenant. A sample query is as follows:

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')
   )
  );

The following sample code queries related information about the SP2 subpartition 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 the transfer destination.

Query the CDB_OB_LS view for 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 |
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
|      1006 |     1 | NORMAL | zone1        |             0 |           0 |                NULL |     NULL | 1701244663685197789 | 1701244663685197789 |      |
|      1006 |  1001 | NORMAL | zone1        |          1006 |        1001 | 1701239786827662637 |     NULL | 1701244663685197789 | 1701244663685197789 |      |
|      1006 |  1002 | NORMAL | zone1        |          1007 |        1002 | 1701239786831568305 |     NULL | 1701244664066906860 | 1701244664066906859 |      |
|      1006 |  1003 | NORMAL | zone1        |          1008 |        1003 | 1701239786834300282 |     NULL | 1701244664175263949 | 1701244664175263948 |      |
+-----------+-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+------+
4 rows in set

Query the CDB_OB_TABLET_TO_LS view for the distribution of tablets on the log streams.

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

Select an appropriate log stream as the transfer destination based on the preceding information.

Execute the following statement to perform partition transfer:
```
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] table_id, OBJECT_ID [=] object_id TO LS ls_id TENANT = 'tenant_name';
```
The parameters are described as follows:
- table_id: the ID of the table.
- object_id: the unique ID of the partition.
- ls_id: the ID of the log stream to which the partition is to be transferred.
- tenant_name: the tenant to which the partition to be transferred belongs.
The following sample code transfers the M202005 partition of the TBL1_LOG_R table in the SYS database from log stream 1002 to log stream 1003 in the oracle_tenant tenant.
```
obclient [oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003 TENANT = 'oracle_tenant';
```
After the statement is executed, query the status of the partition transfer task.
1. Query the CDB_OB_TRANSFER_PARTITION_TASKS view for the TASK_ID, TRANSFER_TASK_ID, and BALANCE_JOB_ID values of the task.
  
  The CDB_OB_TRANSFER_PARTITION_TASKS view displays the ongoing partition transfer tasks of all tenants. A sample query is as follows:
```
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;
```
  A sample query result is as follows:
```
+---------+----------------+------------------+--------+
| TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS |
+---------+----------------+------------------+--------+
|       1 |          17304 |                1 | DOING  |
+---------+----------------+------------------+--------+
1 row in set
```
  The value of the STATUS field indicates the status of the task. Valid values:
  - WAITING: The task is waiting to be scheduled.
  - INIT: BALANCE_JOB_ID has been generated for the task. You can query the execution progress of the associated load balancing job based on BALANCE_JOB_ID.
  - DOING: The task is in progress. You can view the task based on TRANSFER_TASK_ID, which will change several times. A partition transfer task involves multiple transfers.
  If the query on the CDB_OB_TRANSFER_PARTITION_TASKS view returns an empty result set, you can query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view for the task execution result.
2. Query the CDB_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOB_HISTORY view based on the obtained BALANCE_JOB_ID value to confirm the execution status of the associated load balancing job.
  
  The CDB_OB_BALANCE_JOBS view displays the ongoing load balancing jobs of all tenants. Each tenant can have only one running load balancing job (BALANCE_JOB) at a time. A load balancing job generates multiple transfer tasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view displays the historical load balancing jobs of all tenants. Sample queries are as follows:
```
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;
```
  You must replace 17304 with the BALANCE_JOB_ID value obtained in previous steps.
  
  The STATUS column in the query result indicates the execution status of the load balancing job. Valid values:
  - DOING: The load balancing job is in progress.
  - COMPLETED: The load balancing job was successfully executed.
  - CANCELING: The load balancing job is being canceled.
  - CANCELED: The load balancing job has been canceled.
3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view based on the obtained TRANSFER_TASK_ID value for the execution status of the transfer task.
  
  The CDB_OB_TRANSFER_TASKS view displays the ongoing transfer tasks of all tenants. Multiple ongoing transfer tasks (TRANSFER_TASK) that belong to the same load balancing job (BALANCE_JOB) can exist concurrently in a tenant. The CDB_OB_TRANSFER_TASK_HISTORY view displays the historical transfer tasks of all tenants. Sample queries are as follows:
```
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;
```
  You must replace 1 with the TRANSFER_TASK_ID value obtained in previous steps.
  
  The STATUS column in the query result indicates the execution status of the transfer task. Valid values:
  - INIT: The task is being created.
  - START: The task starts to be executed.
  - DOING: The task is in progress.
  - ABORTED: The task was aborted.
  - COMPLETED: The task was successfully executed.
  - FAILED: The task failed.
  - CANCELED: The task has been canceled.
Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view based on the obtained TASK_ID value for the execution result of the partition transfer task.

The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view displays the historical partition transfer tasks of all tenants.
```
obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
```
You must replace 1 with the TASK_ID value obtained in previous steps.

The STATUS column in the query result indicates the execution result of the partition transfer task. Valid values:
- COMPLETED: The task was successfully executed.
- FAILED: The task failed. You can view the cause of the failure in the COMMENT column.

After successful execution of the partition transfer task, query the information about the partition 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

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