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

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

Limitations and considerations

• After modifying dynamic partition management parameters, pre-created partitions and deleted expired partitions will not take effect immediately. You must wait for the next automatic scheduling of a 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 after specification.

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

  • For a table 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 a table without dynamic partition management enabled (for example, a regular partitioned table), 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 Oracle-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 table modification syntax, see ALTER TABLE.

Examples

1. Create a table.

   -- Create table: TIME_UNIT='hour'
   CREATE TABLE test_tbl1 (col1 INT, col2 TIMESTAMP)
       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 (col2)(
           PARTITION P0 VALUES LESS THAN (TIMESTAMP '2024-11-11 13:30:00')
           );
   

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

   SELECT * FROM sys.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 |
   +---------------+------------+----------+----------------------------------------+--------+-----------+----------------+-------------+-----------+------------------+
   | SYS           | TEST_TBL1  |   507940 | Timestamp '2024-11-11 13:30:00.000000' | 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:

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

   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 sys.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 |
   +---------------+------------+----------+----------------------------------------+--------+-----------+----------------+-------------+-----------+------------------+
   | SYS           | TEST_TBL1  |   507940 | Timestamp '2024-11-11 13:30:00.000000' | 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 automatic scheduling of a dynamic partition management task or manually schedule one. For more details, see Dynamic partition management tasks.

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

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

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

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

      • Partition boundaries are divided by day.
      • Pre-create partitions within 3 days after the current time.
      • Delete partitions whose upper bound 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 yet 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, HIGH_VALUE
   FROM SYS.USER_TAB_PARTITIONS
   WHERE TABLE_NAME = 'TEST_TBL3'
   ORDER BY PARTITION_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 a dynamic partitioned table
• Dynamic partition management tasks