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 in 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 before the current one is completed.

• If a balance job (BALANCE_JOB) is already being processed in the current cluster, manually triggered Transfer Partition tasks will not be scheduled 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-compatible mode and Privilege types in Oracle-compatible 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 a non-partitioned table named T1 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 only 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)
        );
     

     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 both the name of the partition and the name of 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')
        )
       );
     

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

   2. Query the CDB_OB_TABLET_TO_LS view to obtain 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 set
      

      Based on the information displayed in the preceding query results, select an appropriate log stream as the destination for data transfer.

5. Execute the following command to perform the partition transfer.

   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 on 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 log stream 1002 to 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 partition transfer command is successfully 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 currently being processed by all 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 is as follows:

      • WAITING: indicates that the task is waiting and has not been scheduled yet.
      • INIT: indicates that the task has been built with a BALANCE_JOB_ID. You can view the execution progress of the associated BALANCE_JOB_ID based on the BALANCE_JOB_ID.
      • DOING: indicates that the task has started to transfer data. You can view the execution progress of the associated transfer partition task based on the TRANSFER_TASK_ID. The TRANSFER_TASK_ID changes multiple times during a transfer partition task. 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 tasks that are currently being executed by all tenants. Each tenant has only one load balancing task (BALANCE_JOB) at a time. Each load balancing task generates multiple load balancing subtasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view displays the history of all load balancing tasks that have been executed by all 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, 17304 needs to be replaced 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 has been successfully executed.
      • 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 view the execution status of the associated TRANSFER_TASK.

      The CDB_OB_TRANSFER_TASKS view displays all load balancing tasks that are currently being executed for all tenants. At any given time, multiple load balancing tasks (TRANSFER_TASK) may be running, and they all belong to the same load balancing job (BALANCE_JOB). The CDB_OB_TRANSFER_TASK_HISTORY view displays the history of all load balancing tasks that have been 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 query, 1 should be replaced with the TRANSFER_TASK_ID obtained from 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 executed.
      • 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 history of all Transfer Partition tasks that have been executed for all tenants.

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

      In the query, 1 should be replaced with the TASK_ID obtained from 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 executed.

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

7. After the Transfer Partition task is successfully executed, 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 has been successfully migrated from log stream 1002 to log stream 1003.

References

For more information about the Transfer Partition feature, see Transfer Partition.