Modify a dynamic partitioned table

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

This topic describes how to modify a dynamic partitioned table by using SQL statements.

Limitations and considerations

After modifying the dynamic partition management parameters, the actions of pre-creating partitions and deleting expired partitions will not take effect immediately. You need to wait for the next automatic scheduling of the dynamic partition management task, or manually trigger the dynamic partition management task. For more information about how to schedule a dynamic partition management task, see Dynamic partition management task.

Privilege requirements

To modify a dynamic partitioned table, you need the ALTER privilege. For more information about privileges in OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

The SQL statement format for creating 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}'}

Dynamic partition management attributes

For more information about how to modify the syntax of a table, see ALTER TABLE.

Examples

1. Query the dynamic partitioning information about 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
   

2. Modify the dynamic partitioning settings for the test_tbl1 table as follows:

   • Precreate partitions for one day starting from the current time.
   • Partitions never expire.

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

3. Query the dynamic partitioning information about 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 the dynamic partition management parameters, the actions of pre-creating partitions and deleting expired partitions will not take effect immediately. You need to wait for the next automatic scheduling of the dynamic partition management task, or manually trigger the dynamic partition management task. For more information, see Dynamic partition management task.

References

• Overview of dynamic partitioning
• Create a dynamic partitioned table
• Query a dynamic partitioned table
• Manage dynamic partitions