Dynamic partitioning|V4.3.5|OceanBase Database| docs|Distributed Database

Core value

AP scenarios (analytical scenarios) typically face the following challenges:

Massive data growth: Logs, user behavior, and sensor data are continuously written.
Complex resource management: Manually creating/deleting partitions is time-consuming and error-prone.
Storage cost pressure: Automatic cleanup of historical data is needed to prevent storage bloat.

Solution for dynamic partitioning

By setting predefined rules (such as time units, pre-creation cycles, and expiration times), OceanBase automatically manages partitions, achieving:

Zero operational overhead: Pre-creates partitions through scheduled tasks, eliminating the need for manual intervention. (Dynamic partitioning pre-creates partitions based on predefined rules through scheduled tasks; OceanBase 4.4.2/4.5.0 introduces Interval partitioning, which automatically creates partitions during data writes.)
Smart cleanup: Deletes old partitions as needed to free up storage space.
Query acceleration: Partition pruning reduces the amount of data scanned.

Features

Regularly creates new partitions based on TIME_UNIT (such as hours, days, months), ensuring automatic expansion of data by time range.
Partitions are divided based on time units (such as hour, day), suitable for scenarios where data is stored in a time series (such as logs and transaction records).
Specifies the duration for which future partitions should be pre-created using PRECREATE_TIME, preventing partition shortages due to sudden data spikes.
Regularly cleans up expired partitions based on EXPIRE_TIME to free up storage space.
Supports custom time zones (TIME_ZONE) to ensure consistency across different time zones.
Supports precision configuration for bigint type timestamps (such as BIGINT_PRECISION) to meet different time precision requirements.

Key scenarios

Modifying dynamic partitioning properties

Changing business requirements
- To enable or disable dynamic partitioning management, adjust the ENABLE property.
- To retain data for a longer period (e.g., from 30 to 90 days), adjust the dynamic partitioning EXPIRE_TIME property.
Handling business peaks
- During promotions, to avoid partition shortages, adjust the dynamic partitioning PRECREATE_TIME property to pre-create more partitions.

Example of modifying dynamic partitioning properties:

-- Set the pre-creation cycle to 3 days, expiration time to 90 days, and enable dynamic partitioning
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(
    PRECREATE_TIME = '3 DAY',
    EXPIRE_TIME = '90 DAY',
    ENABLE = TRUE
);

-- Temporarily disable dynamic partitioning management
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(ENABLE = FALSE);

Extended configuration scenarios for dynamic partitioning

Scenario: Compliance requirements for extended data retention

-- Extend the expiration time to 90 days
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(
    EXPIRE_TIME = '90 DAY'
);

Partition management

OceanBase manages dynamic partitions through built-in scheduled tasks and supports manual adjustments in the following scenarios:

Adjusting the scheduled task's execution time to avoid business peaks:
```
CALL DBMS_SCHEDULER.SET_ATTRIBUTE(
    'SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY',
    'START_DATE',
    '2025-11-14 02:00:00'
  );
```
- Assume the current time is 2025-11-13 15:00:00 and the default next execution time is 2025-11-14 00:00:00. To adjust the next execution time to 2:00 AM daily, change the next execution time to 2025-11-14 02:00:00.
Special business requirements:
```
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
```
- Manually execute a dynamic partitioning management task to immediately clean up expired partitions or pre-create future partitions (e.g., for urgent storage recovery).

Designing a dynamic partitioning table

CREATE TABLE user_behavior (
    user_id BIGINT,
    event_time DATETIME NOT NULL,
    event_type VARCHAR(50),
    device_info VARCHAR(255)
)
DYNAMIC_PARTITION_POLICY(
    ENABLE = TRUE,
    TIME_UNIT = 'hour',
    PRECREATE_TIME = '3 hour',
    EXPIRE_TIME = '1 DAY',
    TIME_ZONE = '+8:00'
)
PARTITION BY RANGE COLUMNS(event_time) (
    PARTITION p_start VALUES LESS THAN ('2025-11-13 13:30:00')
);

Validate the dynamic partitioning strategy

Step 1: Validate table configuration

show create table user_behavior\G

The output is as follows:

*************************** 1. row ***************************
       Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
  `user_id` bigint(20) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
 partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'))
1 row in set

Validate the partitions:

-- Trigger partition extension, wait for the system to automatically schedule, or manually execute it in the test environment:
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
SHOW CREATE TABLE user_behavior\G

When at least one partition definition is specified, pre-created partitions will not be immediately created after table creation. Instead, they will be created during a dynamic partition management scheduling or manually executed. During dynamic partition management, the system will create partitions at intervals of time_unit until the maximum partition boundary exceeds now() + precreate_time. The first partition boundary created will be the ceiling of the existing maximum partition boundary based on time_unit. For example, if the current time is 2025-11-13 14:50:00, after scheduling dynamic partition management, the expected partition results are as follows:

*************************** 1. row ***************************
       Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
  `user_id` bigint(20) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
 partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'),
partition `P2025111313` values less than ('2025-11-13 14:00:00'),
partition `P2025111314` values less than ('2025-11-13 15:00:00'),
partition `P2025111315` values less than ('2025-11-13 16:00:00'),
partition `P2025111316` values less than ('2025-11-13 17:00:00'),
partition `P2025111317` values less than ('2025-11-13 18:00:00'))
1 row in set

If the existing maximum partition boundary is earlier than the current time, the system will attempt to supplement historical partitions. For instance, if the existing maximum partition is PARTITION P0 VALUES LESS THAN ('2025-11-13 11:30:00') and the current time is 2025-11-13 12:34:56, after scheduling dynamic partition management, the partitions will be:

PARTITION P0 VALUES LESS THAN ('2025-11-13 11:30:00'),
PARTITION P2025111311 VALUES LESS THAN ('2025-11-13 12:00:00'),
PARTITION P2025111312 VALUES LESS THAN ('2025-11-13 13:00:00'),
PARTITION P2025111313 VALUES LESS THAN ('2025-11-13 14:00:00'),
PARTITION P2025111314 VALUES LESS THAN ('2025-11-13 15:00:00'),
PARTITION P2025111315 VALUES LESS THAN ('2025-11-13 16:00:00')

Step 2: Insert test data

-- Insert multiple types of test data to cover different time ranges.
-- Assume the current time is 2025-11-13 15:00:00.

-- (a) Insert data for the current hour (should fall into P2025111315)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1001, '2025-11-13 15:25:00', 'click', 'iPhone15');

-- (b) Insert data for the next hour (should fall into P2025111316, which has been pre-created)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1002, '2025-11-13 16:10:00', 'purchase', 'MacBook');

-- (c) Insert data from 25 hours ago (2025-11-12 13:00:00, which falls into the first partition)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1003, '2025-11-12 13:00:00', 'old_event', 'Android');

-- (d) Insert data that falls exactly within the initial partition (< 13:30)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1004, '2025-11-13 13:20:00', 'view', 'iPad');

-- View the partition extension
SHOW CREATE TABLE user_behavior\G

Example output:

*************************** 1. row ***************************
       Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
  `user_id` bigint(20) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
 partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'),
partition `P2025111313` values less than ('2025-11-13 14:00:00'),
partition `P2025111314` values less than ('2025-11-13 15:00:00'),
partition `P2025111315` values less than ('2025-11-13 16:00:00'),
partition `P2025111316` values less than ('2025-11-13 17:00:00'),
partition `P2025111317` values less than ('2025-11-13 18:00:00'),
partition `P2025111318` values less than ('2025-11-13 19:00:00'))
1 row in set

Step 3: Query validation

-- Query the number of active users in the last 2 hours
SELECT
    DATE_FORMAT(event_time, '%Y-%m-%d %H:00') AS hour_slot,
    COUNT(*) AS event_count,
    COUNT(DISTINCT user_id) AS active_users
FROM user_behavior
WHERE event_time >= NOW() - INTERVAL 2 HOUR
GROUP BY hour_slot
ORDER BY hour_slot;

Sample data:

+------------------+-------------+--------------+
| hour_slot        | event_count | active_users |
+------------------+-------------+--------------+
| 2025-11-13 13:00 |           1 |            1 |
| 2025-11-13 15:00 |           1 |            1 |
| 2025-11-13 16:00 |           1 |            1 |
+------------------+-------------+--------------+
3 rows in set

Step 4: Verify data cleanup for expired data (optional)

Wait for more than 24 hours or manually advance the system time (in a test environment) and trigger the management again:

-- Assume the current time is 2025-11-14 15:00:00
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();

-- Check if partitions before 2025-11-13 14:00 have been removed
SHOW CREATE TABLE user_behavior\G

Step 5: Monitor partition management tasks

Example output:

SELECT JOB_NAME, ENABLED, NEXT_RUN_DATE
FROM oceanbase.DBA_SCHEDULER_JOBS  
WHERE JOB_NAME = 'SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY';

Expected output:

+-------------------------------------------+---------+----------------------------+
| JOB_NAME                                  | ENABLED | NEXT_RUN_DATE              |
+-------------------------------------------+---------+----------------------------+
| SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY | 1       | 2025-11-13 16:00:00.000000 |
+-------------------------------------------+---------+----------------------------+
1 row in set

Step 6: View task execution history

SELECT
    TIMESTAMP,
    VALUE1 AS success_tables,
    VALUE2 AS failed_tables,
    COST_TIME,
    RET_CODE
FROM oceanbase.DBA_OB_TENANT_EVENT_HISTORY
WHERE event = 'MANAGE_DYNAMIC_PARTITION'
ORDER BY TIMESTAMP DESC
LIMIT 3;

Expected output:

+----------------------------+--------------------------+---------------+-----------+----------+
| TIMESTAMP                  | success_tables           | failed_tables | COST_TIME | RET_CODE |
+----------------------------+--------------------------+---------------+-----------+----------+
| 2025-11-13 15:00:00.173069 | [500020, 500053]         | []            |    164938 |        0 |
| 2025-11-13 14:47:56.816717 | [500003, 500020, 500053] | []            |     63801 |        0 |
| 2025-11-13 14:00:00.125075 | [500020]                 | []            |    117113 |        0 |
+----------------------------+--------------------------+---------------+-----------+----------+
3 rows in set

This can be used to troubleshoot whether the partition management task was successfully executed.

Step 7: Verify the behavior of inserting data beyond the pre-created range (optional)

Try to insert data beyond the pre-created range. For example, if the current time is 2025-11-13 15:07:00 and the pre-created upper limit is 2025-11-13 19:00:00, insert data at 2025-11-13 20:00:00:

INSERT INTO user_behavior VALUES (999, '2025-11-13 20:00:00', 'future', 'test');
-- Error: ERROR 1526 (HY000): Table has no partition for value

Data dictionary views

You can query the DBA_OB_DYNAMIC_PARTITION_TABLES data dictionary view to obtain the attributes of a dynamic partition table.

The DBA_OB_DYNAMIC_PARTITION_TABLES view is visible to all tenants and contains the dynamic partition table data of the current tenant.
The oceanbase.CDB_OB_DYNAMIC_PARTITION_TABLES view is visible only to the sys tenant and contains the dynamic partition table data of all tenants.

Dynamic performance views

You can query the dynamic performance view V$OB_DYNAMIC_PARTITION_TABLES to obtain the attributes of a dynamic partition table from memory. This view has better query performance.

In the sys tenant, V$OB_DYNAMIC_PARTITION_TABLES displays the dynamic partition table data of all tenants.
In a user tenant, this view displays only the dynamic partition table data of the current tenant.

References

MySQL mode:
Oracle mode:

Core value

AP scenarios (analytical scenarios) typically face the following challenges:

Massive data growth: Logs, user behavior, and sensor data are continuously written.
Complex resource management: Manually creating/deleting partitions is time-consuming and error-prone.
Storage cost pressure: Automatic cleanup of historical data is needed to prevent storage bloat.

Solution for dynamic partitioning

By setting predefined rules (such as time units, pre-creation cycles, and expiration times), OceanBase automatically manages partitions, achieving:

Zero operational overhead: Pre-creates partitions through scheduled tasks, eliminating the need for manual intervention. (Dynamic partitioning pre-creates partitions based on predefined rules through scheduled tasks; OceanBase 4.4.2/4.5.0 introduces Interval partitioning, which automatically creates partitions during data writes.)
Smart cleanup: Deletes old partitions as needed to free up storage space.
Query acceleration: Partition pruning reduces the amount of data scanned.

Features

Regularly creates new partitions based on TIME_UNIT (such as hours, days, months), ensuring automatic expansion of data by time range.
Partitions are divided based on time units (such as hour, day), suitable for scenarios where data is stored in a time series (such as logs and transaction records).
Specifies the duration for which future partitions should be pre-created using PRECREATE_TIME, preventing partition shortages due to sudden data spikes.
Regularly cleans up expired partitions based on EXPIRE_TIME to free up storage space.
Supports custom time zones (TIME_ZONE) to ensure consistency across different time zones.
Supports precision configuration for bigint type timestamps (such as BIGINT_PRECISION) to meet different time precision requirements.

Key scenarios

Modifying dynamic partitioning properties

Changing business requirements
- To enable or disable dynamic partitioning management, adjust the ENABLE property.
- To retain data for a longer period (e.g., from 30 to 90 days), adjust the dynamic partitioning EXPIRE_TIME property.
Handling business peaks
- During promotions, to avoid partition shortages, adjust the dynamic partitioning PRECREATE_TIME property to pre-create more partitions.

Example of modifying dynamic partitioning properties:

-- Set the pre-creation cycle to 3 days, expiration time to 90 days, and enable dynamic partitioning
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(
    PRECREATE_TIME = '3 DAY',
    EXPIRE_TIME = '90 DAY',
    ENABLE = TRUE
);

-- Temporarily disable dynamic partitioning management
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(ENABLE = FALSE);

Extended configuration scenarios for dynamic partitioning

Scenario: Compliance requirements for extended data retention

-- Extend the expiration time to 90 days
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(
    EXPIRE_TIME = '90 DAY'
);

Partition management

OceanBase manages dynamic partitions through built-in scheduled tasks and supports manual adjustments in the following scenarios:

Adjusting the scheduled task's execution time to avoid business peaks:
```
CALL DBMS_SCHEDULER.SET_ATTRIBUTE(
    'SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY',
    'START_DATE',
    '2025-11-14 02:00:00'
  );
```
- Assume the current time is 2025-11-13 15:00:00 and the default next execution time is 2025-11-14 00:00:00. To adjust the next execution time to 2:00 AM daily, change the next execution time to 2025-11-14 02:00:00.
Special business requirements:
```
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
```
- Manually execute a dynamic partitioning management task to immediately clean up expired partitions or pre-create future partitions (e.g., for urgent storage recovery).

Designing a dynamic partitioning table

CREATE TABLE user_behavior (
    user_id BIGINT,
    event_time DATETIME NOT NULL,
    event_type VARCHAR(50),
    device_info VARCHAR(255)
)
DYNAMIC_PARTITION_POLICY(
    ENABLE = TRUE,
    TIME_UNIT = 'hour',
    PRECREATE_TIME = '3 hour',
    EXPIRE_TIME = '1 DAY',
    TIME_ZONE = '+8:00'
)
PARTITION BY RANGE COLUMNS(event_time) (
    PARTITION p_start VALUES LESS THAN ('2025-11-13 13:30:00')
);

Validate the dynamic partitioning strategy

Step 1: Validate table configuration

show create table user_behavior\G

The output is as follows:

*************************** 1. row ***************************
       Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
  `user_id` bigint(20) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
 partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'))
1 row in set

Validate the partitions:

-- Trigger partition extension, wait for the system to automatically schedule, or manually execute it in the test environment:
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();
SHOW CREATE TABLE user_behavior\G

*************************** 1. row ***************************
       Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
  `user_id` bigint(20) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
 partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'),
partition `P2025111313` values less than ('2025-11-13 14:00:00'),
partition `P2025111314` values less than ('2025-11-13 15:00:00'),
partition `P2025111315` values less than ('2025-11-13 16:00:00'),
partition `P2025111316` values less than ('2025-11-13 17:00:00'),
partition `P2025111317` values less than ('2025-11-13 18:00:00'))
1 row in set

PARTITION P0 VALUES LESS THAN ('2025-11-13 11:30:00'),
PARTITION P2025111311 VALUES LESS THAN ('2025-11-13 12:00:00'),
PARTITION P2025111312 VALUES LESS THAN ('2025-11-13 13:00:00'),
PARTITION P2025111313 VALUES LESS THAN ('2025-11-13 14:00:00'),
PARTITION P2025111314 VALUES LESS THAN ('2025-11-13 15:00:00'),
PARTITION P2025111315 VALUES LESS THAN ('2025-11-13 16:00:00')

Step 2: Insert test data

-- Insert multiple types of test data to cover different time ranges.
-- Assume the current time is 2025-11-13 15:00:00.

-- (a) Insert data for the current hour (should fall into P2025111315)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1001, '2025-11-13 15:25:00', 'click', 'iPhone15');

-- (b) Insert data for the next hour (should fall into P2025111316, which has been pre-created)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1002, '2025-11-13 16:10:00', 'purchase', 'MacBook');

-- (c) Insert data from 25 hours ago (2025-11-12 13:00:00, which falls into the first partition)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1003, '2025-11-12 13:00:00', 'old_event', 'Android');

-- (d) Insert data that falls exactly within the initial partition (< 13:30)
INSERT INTO user_behavior (user_id, event_time, event_type, device_info)
VALUES (1004, '2025-11-13 13:20:00', 'view', 'iPad');

-- View the partition extension
SHOW CREATE TABLE user_behavior\G

Example output:

*************************** 1. row ***************************
       Table: user_behavior
Create Table: CREATE TABLE `user_behavior` (
  `user_id` bigint(20) DEFAULT NULL,
  `event_time` datetime NOT NULL,
  `event_type` varchar(50) DEFAULT NULL,
  `device_info` varchar(255) DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
 partition by range columns(`event_time`)
(partition `p_start` values less than ('2025-11-13 13:30:00'),
partition `P2025111313` values less than ('2025-11-13 14:00:00'),
partition `P2025111314` values less than ('2025-11-13 15:00:00'),
partition `P2025111315` values less than ('2025-11-13 16:00:00'),
partition `P2025111316` values less than ('2025-11-13 17:00:00'),
partition `P2025111317` values less than ('2025-11-13 18:00:00'),
partition `P2025111318` values less than ('2025-11-13 19:00:00'))
1 row in set

Step 3: Query validation

-- Query the number of active users in the last 2 hours
SELECT
    DATE_FORMAT(event_time, '%Y-%m-%d %H:00') AS hour_slot,
    COUNT(*) AS event_count,
    COUNT(DISTINCT user_id) AS active_users
FROM user_behavior
WHERE event_time >= NOW() - INTERVAL 2 HOUR
GROUP BY hour_slot
ORDER BY hour_slot;

Sample data:

+------------------+-------------+--------------+
| hour_slot        | event_count | active_users |
+------------------+-------------+--------------+
| 2025-11-13 13:00 |           1 |            1 |
| 2025-11-13 15:00 |           1 |            1 |
| 2025-11-13 16:00 |           1 |            1 |
+------------------+-------------+--------------+
3 rows in set

Step 4: Verify data cleanup for expired data (optional)

Wait for more than 24 hours or manually advance the system time (in a test environment) and trigger the management again:

-- Assume the current time is 2025-11-14 15:00:00
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();

-- Check if partitions before 2025-11-13 14:00 have been removed
SHOW CREATE TABLE user_behavior\G

Step 5: Monitor partition management tasks

Example output:

SELECT JOB_NAME, ENABLED, NEXT_RUN_DATE
FROM oceanbase.DBA_SCHEDULER_JOBS  
WHERE JOB_NAME = 'SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY';

Expected output:

+-------------------------------------------+---------+----------------------------+
| JOB_NAME                                  | ENABLED | NEXT_RUN_DATE              |
+-------------------------------------------+---------+----------------------------+
| SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY | 1       | 2025-11-13 16:00:00.000000 |
+-------------------------------------------+---------+----------------------------+
1 row in set

Step 6: View task execution history

SELECT
    TIMESTAMP,
    VALUE1 AS success_tables,
    VALUE2 AS failed_tables,
    COST_TIME,
    RET_CODE
FROM oceanbase.DBA_OB_TENANT_EVENT_HISTORY
WHERE event = 'MANAGE_DYNAMIC_PARTITION'
ORDER BY TIMESTAMP DESC
LIMIT 3;

Expected output:

+----------------------------+--------------------------+---------------+-----------+----------+
| TIMESTAMP                  | success_tables           | failed_tables | COST_TIME | RET_CODE |
+----------------------------+--------------------------+---------------+-----------+----------+
| 2025-11-13 15:00:00.173069 | [500020, 500053]         | []            |    164938 |        0 |
| 2025-11-13 14:47:56.816717 | [500003, 500020, 500053] | []            |     63801 |        0 |
| 2025-11-13 14:00:00.125075 | [500020]                 | []            |    117113 |        0 |
+----------------------------+--------------------------+---------------+-----------+----------+
3 rows in set

This can be used to troubleshoot whether the partition management task was successfully executed.

Step 7: Verify the behavior of inserting data beyond the pre-created range (optional)

Try to insert data beyond the pre-created range. For example, if the current time is 2025-11-13 15:07:00 and the pre-created upper limit is 2025-11-13 19:00:00, insert data at 2025-11-13 20:00:00:

INSERT INTO user_behavior VALUES (999, '2025-11-13 20:00:00', 'future', 'test');
-- Error: ERROR 1526 (HY000): Table has no partition for value

Data dictionary views

You can query the DBA_OB_DYNAMIC_PARTITION_TABLES data dictionary view to obtain the attributes of a dynamic partition table.

The DBA_OB_DYNAMIC_PARTITION_TABLES view is visible to all tenants and contains the dynamic partition table data of the current tenant.
The oceanbase.CDB_OB_DYNAMIC_PARTITION_TABLES view is visible only to the sys tenant and contains the dynamic partition table data of all tenants.

Dynamic performance views

You can query the dynamic performance view V$OB_DYNAMIC_PARTITION_TABLES to obtain the attributes of a dynamic partition table from memory. This view has better query performance.

In the sys tenant, V$OB_DYNAMIC_PARTITION_TABLES displays the dynamic partition table data of all tenants.
In a user tenant, this view displays only the dynamic partition table data of the current tenant.

References

MySQL mode:
Oracle mode:

OceanBase

Customer Stories

Documentation

Dynamic partitioning

Core value

Solution for dynamic partitioning

Features

Key scenarios

Modifying dynamic partitioning properties

Extended configuration scenarios for dynamic partitioning

Partition management

Designing a dynamic partitioning table

Validate the dynamic partitioning strategy

Step 1: Validate table configuration

Step 2: Insert test data

Step 3: Query validation

Step 4: Verify data cleanup for expired data (optional)

Step 5: Monitor partition management tasks

Step 6: View task execution history

Step 7: Verify the behavior of inserting data beyond the pre-created range (optional)

Related views

Data dictionary views

Dynamic performance views

References

Dynamic partitioning

Core value

Solution for dynamic partitioning

Features

Key scenarios

Modifying dynamic partitioning properties

Extended configuration scenarios for dynamic partitioning

Partition management

Designing a dynamic partitioning table

Validate the dynamic partitioning strategy

Step 1: Validate table configuration

Step 2: Insert test data

Step 3: Query validation

Step 4: Verify data cleanup for expired data (optional)

Step 5: Monitor partition management tasks

Step 6: View task execution history

Step 7: Verify the behavior of inserting data beyond the pre-created range (optional)

Related views

Data dictionary views

Dynamic performance views

References