Modify a dynamically partitioned table

In OceanBase Database, you can modify the dynamic partition management attributes of a table by altering the DYNAMIC_PARTITION_POLICY block.

This topic describes how to use SQL statements to modify a dynamically partitioned table.

Limitations and considerations

• Changes to dynamic partition management parameters do not take effect immediately for pre-created partitions or deleted expired partitions. You must wait for the next automatically scheduled dynamic partition management task or manually schedule one. For more information about scheduling dynamic partition management tasks, see Dynamic partition management tasks.

• The TIME_UNIT, TIME_ZONE, and BIGINT_PRECISION parameters are specified when creating a dynamically partitioned table and cannot be modified afterward.

• Whether to specify TIME_UNIT when modifying the dynamic partitioning strategy depends on whether dynamic partition management is enabled for the table:

  • For tables with dynamic partition management enabled, you do not need to specify TIME_UNIT and cannot specify it in the modification statement (doing so will cause an error).
  • For tables without dynamic partition management enabled (for example, regular partitioned tables), you need to specify TIME_UNIT in the modification statement.

Privilege requirements

To modify a dynamically partitioned table, you must have the ALTER privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL-compatible mode.

Syntax

The SQL statement format for modifying a dynamically partitioned table is as follows:

ALTER TABLE table_name [SET] DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list);

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}

Description of dynamic partition management attribute parameters

For more detailed parameter descriptions related to modifying table syntax, see ALTER TABLE.

Examples

1. Create a table:

   CREATE TABLE test_tbl1 (col1 INT, col2 DATETIME)
   DYNAMIC_PARTITION_POLICY(
       ENABLE = true,
       TIME_UNIT = 'hour',
       PRECREATE_TIME = '3 hour',
       EXPIRE_TIME = '1 day',
       TIME_ZONE = '+8:00',
       BIGINT_PRECISION = 'none'
   )
   PARTITION BY RANGE COLUMNS (col2)(
       PARTITION P0 VALUES LESS THAN ('2025-04-15 13:30:00')
   );
   

2. View the dynamic partition information of the test_tbl1 table.

   SELECT * FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
   WHERE TABLE_NAME = 'test_tbl1';
   

   The query result is as follows:

   +---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
   | DATABASE_NAME | TABLE_NAME | TABLE_ID | MAX_HIGH_BOUND_VAL    | ENABLE | TIME_UNIT | PRECREATE_TIME | EXPIRE_TIME | TIME_ZONE | BIGINT_PRECISION |
   +---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
   | db_test       | test_tbl1  |   511335 | '2025-04-15 13:30:00' | TRUE   | HOUR      | 3HOUR          | 1DAY        | +8:00     | NONE             |
   +---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
   1 row in set
   

3. Modify the dynamic partition table test_tbl1 and set its dynamic partition attributes to be:

   • Pre-create partitions within 1 day after the current time.
   • Partitions are never expired.

   ALTER TABLE test_tbl1 DYNAMIC_PARTITION_POLICY(
       ENABLE = true,
       PRECREATE_TIME = '1 day',
       EXPIRE_TIME = '-1'
   );
   

4. View the dynamic partition information of the test_tbl1 table again.

   SELECT * FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
   WHERE TABLE_NAME = 'test_tbl1';
   

   The query result is as follows:

   +---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
   | DATABASE_NAME | TABLE_NAME | TABLE_ID | MAX_HIGH_BOUND_VAL    | ENABLE | TIME_UNIT | PRECREATE_TIME | EXPIRE_TIME | TIME_ZONE | BIGINT_PRECISION |
   +---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
   | db_test       | test_tbl1  |   511335 | '2025-04-15 13:30:00' | TRUE   | HOUR      | 1DAY           | -1          | +8:00     | NONE             |
   +---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
   1 row in set
   

   Note

   After modifying dynamic partition management parameters, pre-created partitions and deleted expired partitions do not take effect immediately. You need to wait for the next automatically scheduled dynamic partition management task or manually schedule one. For details, see Dynamic partition management task.

5. Add a dynamic partition strategy to a regular RANGE COLUMNS partitioned table that has not enabled dynamic partition management.

   1. Create a regular RANGE COLUMNS partitioned table named test_tbl3.

      CREATE TABLE test_tbl3 (col1 INT, col2 DATETIME)
          PARTITION BY RANGE COLUMNS (col2)(
              PARTITION P0 VALUES LESS THAN ('2026-09-14 00:00:00')
          );
      

   2. Add a dynamic partition strategy to test_tbl3 and set its dynamic partition attributes to be:

      • Partition boundaries are divided by day.
      • Pre-create partitions within 3 days after the current time.
      • Delete partitions whose upper boundary is earlier than 30 days before the current time.

      ALTER TABLE test_tbl3 DYNAMIC_PARTITION_POLICY(
          ENABLE = true,
          TIME_UNIT = 'day',
          PRECREATE_TIME = '3 day',
          EXPIRE_TIME = '30 day'
      );
      

      Note

      • When adding a dynamic partition strategy to a table that has not enabled dynamic partition management, you must specify TIME_UNIT.
      • When the partitioning key is of the time type, the default value of TIME_ZONE is default, and the default value of BIGINT_PRECISION is none. These can be omitted.

6. Manually trigger a dynamic partition management task to view the pre-created partitions.

   CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
   

   View the partition information of the test_tbl3 table:

   SELECT TABLE_NAME, PARTITION_NAME, PARTITION_DESCRIPTION
   FROM information_schema.PARTITIONS
   WHERE TABLE_NAME = 'test_tbl3'
   ORDER BY PARTITION_ORDINAL_POSITION;
   

   In the query result, you can see the three pre-created daily partitions.

References

• Overview of dynamic partitions
• Create a dynamic partitioned table
• Query dynamic partitioned tables
• Dynamic partition management tasks