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 partitions under user tenants 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 may have their corresponding Transfer Partition tasks 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 or replicating tables to a regular log stream is 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 a balance job (BALANCE_JOB) is already being processed in the current cluster, manually triggered Transfer Partition tasks will not start 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. 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 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)
        );
     

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

      Select a suitable log stream as the destination for data transfer.

5. Execute the following command to perform a 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 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';
   

6. After the partition transfer 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 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 by checking the STATUS column in the query result. The following table describes the values of the STATUS column.

      • WAITING: indicates that the task is waiting to be scheduled.
      • INIT: indicates that the task has been initialized with a BALANCE_JOB_ID. You can view the execution progress of the BALANCE_JOB_ID by checking the BALANCE_JOB_ID column.
      • DOING: indicates that the task is being executed. You can view the execution progress of the TRANSFER_TASK_ID by checking the TRANSFER_TASK_ID column. A TRANSFER_TASK_ID changes frequently, and a partition transfer task may involve 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 by using 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 currently being executed by all tenants. Each tenant has only one load balancing task (BALANCE_JOB) at a time, and each task generates multiple load balancing subtasks (TRANSFER_TASK). The CDB_OB_BALANCE_JOB_HISTORY view displays the history of all load balancing tasks 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 is 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 is canceled.

   3. Query the CDB_OB_TRANSFER_TASKS or CDB_OB_TRANSFER_TASK_HISTORY view by using the obtained TRANSFER_TASK_ID to view the execution status of the associated TRANSFER_TASK.

      The CDB_OB_TRANSFER_TASKS view shows all load balancing tasks currently being executed by all tenants. At the same time, multiple load balancing tasks (TRANSFER_TASK) can be executed, and these tasks all belong to the same load balancing job (BALANCE_JOB). The CDB_OB_TRANSFER_TASK_HISTORY view shows the history of load balancing tasks executed by 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 preceding example, 1 needs to be replaced with the TRANSFER_TASK_ID obtained in a previous step.

      The STATUS column in the query result shows 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.

   4. Query the CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view based on the obtained TASK_ID to confirm the result of the Transfer Partition task.

      The CDB_OB_TRANSFER_PARTITION_TASK_HISTORY view shows the history of Transfer Partition tasks executed by all tenants.

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

      In the preceding example, 1 needs to be replaced with the TASK_ID obtained in a previous step.

      The STATUS column in the query result shows 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 further confirm the cause of the task failure by checking the COMMENT column.

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

   From the query result, you can see that the M202005 partition of the TBL1_LOG_R table in the SYS database was migrated from the previous log stream 1002 to the log stream 1003. This indicates that the Transfer Partition operation was successful.

References

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