In OceanBase AI 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 dynamic partitioned table.
Limitations and considerations
After you modify dynamic partition management parameters, the changes do not take effect immediately for pre-created partitions or for the deletion of 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.
Privilege requirements
You must have the ALTER privilege to modify a dynamic partitioned table. For more information about privileges in OceanBase AI Database, see Permission classification.
Syntax
The SQL statement for modifying a dynamic 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}
| 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
Attribute name |
Description |
Required |
Modifiable |
|---|---|---|---|
| ENABLE | Indicates whether to enable dynamic partition management. Valid values:
|
No | Yes |
| PRECREATE_TIME | Indicates the pre-create time. When scheduling a dynamic partition management task, partitions are pre-created so that max_partition_upper_bound > now() + precreate_time. Valid values:
Note
|
No | Yes |
| EXPIRE_TIME | Indicates the partition expiration time. When dynamic partition management is scheduled, all partitions whose upper_bound < now() - expire_time are deleted. Valid values:
|
No | Yes |
For more detailed parameter descriptions on modifying table syntax, see ALTER TABLE.
Examples
View the dynamic partition information of the
test_tbl1table.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 setModify the dynamic partitioned table
test_tbl1and 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' );View the dynamic partition information of the
test_tbl1table 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 setNote
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 more information, see Dynamic partition management tasks.