Change the attribute of a table

In OceanBase Database, the DUPLICATE_SCOPE parameter is used to specify the attributes of a replicated table. After a table is created, you can change the value of the DUPLICATE_SCOPE parameter to convert it between a regular table and a replicated table.

Limitations and considerations

• If a load balancing task is triggered due to scaling, changing the primary zone, or modifying the locality of the current tenant, the task of converting a table to a replicated table must wait until the balancing task is completed.

  Notice

  The conversion process for replicated tables is asynchronous. After you change the DUPLICATE_SCOPE, you must wait for the background partition balancing task to complete before using the table as the target attribute. For more information about partition balancing, see Intra-tenant balancing.

• Since replicated tables and table groups are mutually exclusive, a table cannot be converted into a replicated table if it belongs to a table group.

• The conversion of a table into a replicated table depends on the load balancing and transfer features of the tenant. If any of the tenant-level parameters enable_rebalance, enable_transfer, and enable_rereplication is set to false, you cannot convert a table into a replicated table. If you set the enable_rebalance or enable_transfer parameter to false and there are ongoing tasks of converting tables into replicated tables, the tasks will be canceled.

Prerequisites

Before you convert a table into a replicated table, make sure that the following parameters are set to True:

• enable_rebalance

  The tenant-level enable_rebalance parameter controls whether to perform load balancing among tenants. In a user tenant, it controls whether to perform load balancing within the tenant. The default value is True. For more information about the enable_rebalance parameter, see enable_rebalance.

• enable_transfer

  The tenant-level enable_transfer parameter controls whether to perform transfer in the tenant. The default value is True. For more information about the enable_transfer parameter, see enable_transfer.

• enable_rereplication

  The cluster-level enable_rereplication parameter controls whether to enable automatic replica supplementation. The default value is True. For more information about the enable_rereplication parameter, see enable_rereplication.

SQL syntax for changing the replicated table attribute

The SQL syntax for changing the replicated table attribute is as follows:

ALTER TABLE table_name DUPLICATE_SCOPE= 'none | cluster';

In this syntax:

• none: specifies to convert the current table into a regular table.
• cluster: specifies to convert the current table into a replicated table.

Example

The following commands show how to convert a replicated table into a regular table.

1. Create a replicated table named tbl1.

   obclient [test]> CREATE TABLE tbl1 (c1 int,c2 int) DUPLICATE_SCOPE= 'cluster';
   

2. Convert the replicated table into a regular table.

   ALTER TABLE tbl1 DUPLICATE_SCOPE= 'none';
   

   Notice

   The preceding SQL statement will immediately return a success message after modifying the table attribute. The actual conversion of the replicated table is asynchronously performed in the background by a thread.

3. View the attributes of the replicated table.

   You can query the sys.DBA_OB_TABLE_LOCATIONS view for the attributes of the replicated table and the log stream ID where the replicated table is located. If the DUPLICATE_SCOPE column displays CLUSTER, the table is a replicated table. If it displays NONE, the table is a regular table.

   SELECT database_name, table_name, table_id, tablet_id, ls_id, role, duplicate_scope
   FROM sys.DBA_OB_TABLE_LOCATIONS
   WHERE table_name = 'TBL1';
   

   The return result is as follows:

   +---------------+------------+----------+-----------+-------+--------+-----------------+
   | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLET_ID | LS_ID | ROLE   | DUPLICATE_SCOPE |
   +---------------+------------+----------+-----------+-------+--------+-----------------+
   | SYS           | TBL1       |   500011 |    200002 |  1002 | LEADER | NONE            |
   +---------------+------------+----------+-----------+-------+--------+-----------------+
   1 row in set
   

   Note

   You can follow the steps below to check whether the partition replicas of the converted table meet the desired attribute.

4. View the conversion progress.

   1. View the attribute of the log stream.

      All replicated tables in a tenant must be placed on a special log stream (broadcast log stream) for replicated tables. You can query the sys.DBA_OB_LS view for the attribute of the log stream. If the FLAG column contains DUPLICATE, the log stream is a broadcast log stream. Otherwise, it is a normal log stream.

      SELECT * FROM sys.DBA_OB_LS WHERE LS_ID = 1002;
      

      The return result is as follows:

       +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
       | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG      | UNIT_LIST |
       +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
       |  1002 | NORMAL | zone1        |             0 |           0 | 1713164474213079001 |     NULL | 1713173927933707000 | 1713173927933707000 | DUPLICATE |           |
       +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+-----------+
       1 row in set
      

   2. Determine whether a table conversion task needs to be generated.

      Table conversion tasks are generated in the background by the load balancer and the transfer module. You can execute the following statement to quickly determine whether a table conversion task needs to be generated. Here, DUPLICATE_SCOPE is the attribute of the table, and FLAG is the attribute of the log stream to which the tablet belongs. If the FLAG column contains DUPLICATE, the log stream is a broadcast log stream.

      SELECT A.duplicate_scope, B.flag
      FROM sys.DBA_OB_TABLE_LOCATIONS A, sys.DBA_OB_LS B
      WHERE A.ls_id = B.ls_id
      AND A.table_name = 'TBL1'
      AND A.role = 'LEADER';
      

      The return result is as follows:

       +-----------------+-----------+
       | DUPLICATE_SCOPE | FLAG      |
       +-----------------+-----------+
       | NONE            | DUPLICATE |
       +-----------------+-----------+
       1 row in set
      

      The return information is described as follows:

      • DUPLICATE_SCOPE is NONE, and FLAG contains DUPLICATE: The non-replicated table is placed on a broadcast log stream. A table conversion task needs to be generated to transfer the non-replicated table from the broadcast log stream to a normal log stream.
      • DUPLICATE_SCOPE is NONE, and FLAG does not contain DUPLICATE: The replicated table is placed on a normal log stream. A table conversion task needs to be generated to transfer the replicated table from a normal log stream to a broadcast log stream.

   3. Generate a table conversion task immediately.

      You can temporarily set the partition_balance_schedule_interval parameter to a smaller value to generate a table conversion task immediately.

      ALTER SYSTEM SET partition_balance_schedule_interval = '30s';
      

   4. View the table conversion task.

      Table conversion tasks can be queried from the DBA_OB_BALANCE_JOB_HISTORY or CDB_OB_BALANCE_JOB_HISTORY view and the DBA_OB_BALANCE_JOBS or CDB_OB_BALANCE_JOBS view.

      1. View the execution status of historical conversion tasks.

         If no historical records are returned, the task is either being executed or has not been generated yet.

         SELECT * FROM sys.DBA_OB_BALANCE_JOB_HISTORY;
         

         The return result is as follows:

         +--------+------------------------------+------------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
         | JOB_ID | CREATE_TIME                  | FINISH_TIME                  | BALANCE_STRATEGY  | JOB_TYPE          | TARGET_UNIT_NUM | TARGET_PRIMARY_ZONE_NUM | STATUS    | COMMENT |
         +--------+------------------------------+------------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
         | 787926 | 15-APR-24 05.41.42.226172 PM | 15-APR-24 05.41.52.235808 PM | partition balance | PARTITION_BALANCE |               1 |                       1 | COMPLETED | NULL    |
         +--------+------------------------------+------------------------------+-------------------+-------------------+-----------------+-------------------------+-----------+---------+
         1 row in set
         

      2. View the execution status of the current balancing task.

         SELECT * FROM sys.DBA_OB_BALANCE_JOBS;
         

      3. View the subtasks (including the partition lists) of the conversion task.

         Replace xxxx in the following SQL statement with the job_id of the task:

         SELECT * FROM sys.DBA_OB_BALANCE_TASKS WHERE JOB_ID = xxxx;
         

         SELECT * FROM sys.DBA_OB_BALANCE_TASK_HISTORY WHERE JOB_ID = xxxx;
         

References

• Create a table
• Modify a table