Purpose
The ALTER SYSTEM TRANSFER PARTITION statement is used to transfer a specified partition to a specified log stream.
Limitations and considerations
The Transfer Partition operation is only supported on primary tenants, and can only transfer partitions under user tenants.
If the tenant's transfer feature is disabled (i.e., the value of the
enable_transferparameter isfalse), executing theTRANSFER PARTITIONstatement for that tenant will result in an error. ForTRANSFER PARTITIONstatements that have already been successfully executed, their corresponding partition transfer tasks may be canceled.Transfer Partition operations are not supported on system tables.
Transfer Partition operations are not supported on user tables under the sys tenant.
Transferring a regular table to a broadcast log stream or a replicated table to a regular log stream is not supported.
Non-independent partitions cannot be transferred. For example, partitions of a local index table or a LOB table cannot be transferred.
For the same partition, another Transfer Partition operation cannot be initiated before the current one is completed.
If there is already a balance task (BALANCE_JOB) being processed in the current cluster, a manually triggered Transfer Partition task will not start scheduling immediately.
Privilege requirements
To execute the ALTER SYSTEM TRANSFER PARTITION statement, the current user must have the ALTER SYSTEM privilege. For detailed information about OceanBase AI Database privileges, see Permission classification.
Syntax
ALTER SYSTEM TRANSFER PARTITION TABLE_ID [=] $table_id, OBJECT_ID [=] $object_id TO LS $ls_id [tenant = '$tenant_name']
Parameters
Parameters |
Description |
|---|---|
| $table_id | The table ID of the partitioned table to be migrated. |
| $object_id | The unique identifier of the partitioned table to be migrated, which must be an integer. |
| $ls_id | Log stream ID at the destination of the transfer partition. |
| $tenant_name | Tenant name. When executing this SQL statement under the system tenant, you must specify a tenant name. |
Examples
Log in to the
systenant of a cluster as therootuser.The following is an example connection string. Please use the actual environment information when connecting to the database.
obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -AQuery the
DBA_OB_TENANTSview for theTENANT_IDof the target tenant.obclient [oceanbase]> SELECT TENANT_ID FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'mysql_tenant';The query result is as follows:
+-----------+ | TENANT_ID | +-----------+ | 1006 | +-----------+ 1 row in setConfirm partition information.
You can query the
CDB_OB_TABLE_LOCATIONSview for theTABLE_ID,OBJECT_ID, andLS_IDof a specified partition.Get the
TABLE_ID,OBJECT_ID, andLS_IDof a non-partitioned tableAssume that there is a non-partitioned table named
T1in themysql_tenanttenant. The query example 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 setGet the
TABLE_ID,OBJECT_ID, andLS_IDof a partitioned tableFor a partitioned table, you only need to specify the name of the partition when querying.
Assume that there is a partitioned table named
TBL1_LOG_Rin themysql_tenanttenant.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 example shows how to query the
M202005partition of this 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 setGet the
TABLE_ID,OBJECT_ID, andLS_IDof a subpartitioned tableFor a subpartitioned table, you need to specify both the partition name and the subpartition name when querying.
Assume that a subpartitioned table named
T2_F_RLexists in themysql_tenanttenant.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 example queries information related to the subpartition
SP2in this 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 destination for the transfer.
Query the
CDB_OB_LSview for the status and information of the tenant's log streams.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 setQuery the
CDB_OB_TABLET_TO_LSview for the distribution information of tablets 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 setBased on the information displayed above, select an appropriate log stream as the destination for the transfer.
Run 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 TENANT = 'tenant_name';Where:
table_id: The table ID.object_id: The unique identifier of the partition.ls_id: The log stream ID at the destination of the transfer.tenant_name: The tenant to which the partition to be transferred belongs.
The following example shows how to migrate the
M202005partition of theTBL1_LOG_Rtable in theSYSdatabase of themysql_tenanttenant from the current log stream1002to log stream1003.obclient [oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003 TENANT = 'mysql_tenant';After the Transfer Partition command is successfully executed, you can view the task status in the following views.
Query the
CDB_OB_TRANSFER_PARTITION_TASKSview for the task'sTASK_ID,TRANSFER_TASK_ID, andBALANCE_JOB_ID.The
CDB_OB_TRANSFER_PARTITION_TASKSview displays the partition transfer tasks currently being processed by 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;An example of the query result is as follows:
+---------+----------------+------------------+--------+ | TASK_ID | BALANCE_JOB_ID | TRANSFER_TASK_ID | STATUS | +---------+----------------+------------------+--------+ | 1 | 17304 | 1 | DOING | +---------+----------------+------------------+--------+ 1 row in setYou can confirm the task status based on the value of
STATUSin the query results to further view the task execution progress:WAITING: The task is waiting and has not started scheduling.INIT: The task has built theBALANCE_JOB_ID. You can view the execution progress of the associatedBALANCE_JOB_IDusingBALANCE_JOB_ID.DOING: The task has started transferring partitions. You can view the associated partition transfer task by using theTRANSFER_TASK_ID. Note that theTRANSFER_TASK_IDchanges multiple times, as a single partition transfer task involves multiple transfers.
If the query result of the
CDB_OB_TRANSFER_PARTITION_TASKSview is empty, you can view the task result through theCDB_OB_TRANSFER_PARTITION_TASK_HISTORYview.Query the
CDB_OB_BALANCE_JOBSorCDB_OB_BALANCE_JOB_HISTORYview for the associated BALANCE_JOB execution status using the obtainedBALANCE_JOB_ID.The
CDB_OB_BALANCE_JOBSview displays the current load balancing jobs being executed by all tenants. Each tenant has only one balance job (BALANCE_JOB) at a time, and each job generates multiple transfer tasks (TRANSFER_TASK). TheCDB_OB_BALANCE_JOB_HISTORYview displays the historical load balancing jobs executed by all tenants. A sample query is 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;Here,
17304must be replaced with theBALANCE_JOB_IDobtained in the previous step.The
STATUScolumn in the query results shows the execution status of theBALANCE_JOB:DOING: Indicates that load balancing is in progress.COMPLETED: The load balancing task is completed successfully.CANCELING: The load balancing operation is being canceled.CANCELED: The load balancing operation has been canceled.
Query the
CDB_OB_TRANSFER_TASKSorCDB_OB_TRANSFER_TASK_HISTORYview for the task'sTRANSFER_TASK_IDto check the execution status of the associated TRANSFER_TASK.The
CDB_OB_TRANSFER_TASKSview displays all current load balancing tasks for all tenants. Multiple load balancing tasks may be running at the same time, and these tasks (TRANSFER_TASK) all belong to the same load balancing job (BALANCE_JOB). TheCDB_OB_TRANSFER_TASK_HISTORYview displays the history of all load balancing tasks executed by all tenants. A sample query is 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;Here,
1needs to be replaced with theTRANSFER_TASK_IDobtained in the previous step.The
STATUScolumn in the query results shows the execution status of theTRANSFER_TASK:INIT: Indicates that a task is being created.START: Indicates to start the transfer.DOING: The transfer is in progress.ABORTED: The transfer task failed and was terminated.COMPLETED: The transfer task has been successfully executed.FAILED: The transfer task failed.CANCELED: The transfer task is canceled.
Query the
CDB_OB_TRANSFER_PARTITION_TASK_HISTORYview for the obtainedTASK_IDto confirm the result of the partition transfer task.The
CDB_OB_TRANSFER_PARTITION_TASK_HISTORYview displays the history of all partition transfer tasks performed by tenants.obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;Here,
1should be replaced with theTASK_IDobtained in the previous step.The
STATUScolumn in the query results shows the outcome of the transfer partition task:COMPLETED: The transfer partition task was completed successfully.FAILED: The transfer partition task failed. You can check theCOMMENTcolumn for the specific failure reason.
After the Transfer Partition task is successfully executed, check the 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 setThe result shows that the
M202005partition of theTBL1_LOG_Rtable in theSYSdatabase has been successfully migrated from log stream1002to log stream1003. The Transfer Partition operation succeeded.
References
For detailed operations and instructions on transfer partition, see Transfer Partition.