TRANSFER PARTITION

Purpose

The ALTER SYSTEM TRANSFER PARTITION statement is used to migrate a specified partition to a specified log stream.

Limitations and considerations

• Transfer Partition operations can only be performed on the primary tenant, and only user tenant partitions can be transferred.

• If the Transfer feature is disabled for a tenant (i.e., the value of the enable_transfer configuration item is false), executing the TRANSFER PARTITION statement for that tenant will result in an error. Additionally, any previously executed TRANSFER PARTITION statements for that tenant may be canceled.

• Transfer Partition operations are not supported for system tables.

• Transfer Partition operations are not supported for user tables under the system tenant.

• Transferring regular tables to a broadcast log stream and replicating tables to a regular log stream are not supported.

• Transferring non-independent partitions, such as partitions of local index tables and LOB tables, is not supported.

• For the same partition, initiating another Transfer Partition operation is not allowed until the current operation is completed.

• If there is already a balancing task (BALANCE_JOB) running in the current cluster, manually triggered Transfer Partition tasks 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 more information about OceanBase Database privileges, 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']

Parameters

Examples

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

   The following example shows how to connect to the database. Please replace the placeholders with the actual values in your environment.

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

2. 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
   

3. 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.

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

     Assume that the T1 table exists in the oracle_tenant tenant. 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 set
     

   • Obtain the TABLE_ID, OBJECT_ID, and LS_ID of a partitioned table

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

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

     The following example shows how to query the information about the M202005 partition of the TBL1_LOG_R 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 a subpartitioned table

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

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

     The following example shows how to query the information about the SP2 subpartition of the T2_F_RL 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
     

4. Select the log stream as the destination for data transfer.

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

   2. Query the CDB_OB_TABLET_TO_LS view to obtain the tablet distribution 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 for data transfer.

5. 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, to 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, execute the following command:

   obclient [oceanbase]> ALTER SYSTEM TRANSFER PARTITION TABLE_ID = 500003, OBJECT_ID = 500009 TO LS 1003 TENANT = 'oracle_tenant';
   

6. After the transfer partition command is executed, you can query the following views to view the task status.

   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 tenants. The query example 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;
      

      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 can be further viewed as follows:

      • WAITING: The task is waiting to be scheduled.
      • INIT: The task has been initialized with a BALANCE_JOB_ID. You can view the execution progress of the BALANCE_JOB_ID based on the BALANCE_JOB_ID.
      • DOING: The task is being executed. You can view the execution progress of the associated transfer partition task based on the TRANSFER_TASK_ID. The TRANSFER_TASK_ID changes frequently. 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 view the execution status of the associated balance job.

      The CDB_OB_BALANCE_JOBS view displays all load balancing jobs that are being executed by 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 tenants. The query examples 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;
      

      In the preceding examples, 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: The load balancing job is being executed.
      • COMPLETED: The load balancing job is completed.
      • CANCELING: The load balancing job is being canceled.
      • CANCELED: The load balancing job is canceled.

   3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view based on the obtained TRANSFER_TASK_ID to view the execution status of the associated transfer task.

      The CDB_OB_TRANSFER_TASKS view displays all load balancing tasks currently being executed for all tenants. At the same time, multiple load balancing tasks (TRANSFER_TASK) can be executed, and they all belong to the same load balancing job (BALANCE_JOB). The CDB_OB_TRANSFER_TASK_HISTORY view displays the history of load balancing tasks executed for 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 above 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 is starting.
      • DOING: indicates that the Transfer is being executed.
      • 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.

7. 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 history of Transfer Partition tasks executed for all tenants.

   obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TRANSFER_PARTITION_TASK_HISTORY WHERE TASK_ID = 1;
   

   In the above 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 was executed successfully.

   • FAILED: indicates that the Transfer Partition task failed. You can check the COMMENT column to further confirm the reason for the task failure.

8. 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.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 indicates that the M202005 partition of the TBL1_LOG_R table in the SYS database was successfully migrated from log stream 1002 to log stream 1003.

References

For more information about how to transfer partitions, see Transfer Partition.