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

Core values

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 required to prevent storage bloat.

Solution for dynamic partitioning

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

Zero O&M expansion: Precreates partitions through scheduled tasks without manual intervention. (Dynamic partitioning creates partitions based on predefined rules through scheduled tasks; OceanBase Database V4.4.2 and V4.5.0 support interval partitioning, which automatically creates partitions when data is written.)
Smart cleanup: Deletes old partitions as needed to free up storage space.
Query acceleration: Reduces the amount of data scanned through partition pruning.

Features

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

Key scenarios

Modify dynamic partition attributes

Changing business requirements
- When you need to enable or disable dynamic partition management, you can adjust the ENABLE attribute.
- When you need to retain data for a longer period (such as from 30 days to 90 days), you can adjust the dynamic partition EXPIRE_TIME attribute.
Handling business peaks
- During promotions, to avoid partition shortages, you can adjust the dynamic partition PRECREATE_TIME attribute to precreate more partitions.

Here are some examples of modifying dynamic partition attributes:

-- Modify the precreation cycle to 3 days, the 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 partition management
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(ENABLE = FALSE);

Extended configuration scenarios for dynamic partitions

Scenario: Extend data retention period due to compliance requirements

-- 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. In the following scenarios, you can manually adjust the settings:

Change 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 it to execute at 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 partition management task to immediately clean up expired partitions or precreate future partitions (such as for urgent storage recovery).

Designing a dynamic partition 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')
);

Verify the dynamic partitioning strategy

Step 1: Verify the 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

Verify 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, the pre-created partitions will not be immediately created after table creation. Instead, they will be created during a dynamic partition management scheduling or when 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 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 one dynamic partition management scheduling, the expected partition result 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'),
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 example, 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 one dynamic partition management scheduling, 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 test data covering 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 within the initial partition boundary (before 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 verification

-- 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;

Data result:

+------------------+-------------+--------------+
| 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 the cleanup of 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 the partition management task

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 the 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 at the partition boundary (optional)

Try to insert data beyond the pre-created range. Assume the current time is 2025-11-13 15:07:00, and you want to insert data at 2025-11-13 20:00:00, which is beyond the pre-created upper limit of 2025-11-13 19:00:00:

INSERT INTO user_behavior VALUES (999, '2025-11-13 20:00:00', 'future', 'test');
-- The following error will be generated: 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. The view reads data from memory and has better query performance.

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

References

MySQL mode:
Oracle mode:

Core values

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 required to prevent storage bloat.

Solution for dynamic partitioning

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

Zero O&M expansion: Precreates partitions through scheduled tasks without manual intervention. (Dynamic partitioning creates partitions based on predefined rules through scheduled tasks; OceanBase Database V4.4.2 and V4.5.0 support interval partitioning, which automatically creates partitions when data is written.)
Smart cleanup: Deletes old partitions as needed to free up storage space.
Query acceleration: Reduces the amount of data scanned through partition pruning.

Features

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

Key scenarios

Modify dynamic partition attributes

Changing business requirements
- When you need to enable or disable dynamic partition management, you can adjust the ENABLE attribute.
- When you need to retain data for a longer period (such as from 30 days to 90 days), you can adjust the dynamic partition EXPIRE_TIME attribute.
Handling business peaks
- During promotions, to avoid partition shortages, you can adjust the dynamic partition PRECREATE_TIME attribute to precreate more partitions.

Here are some examples of modifying dynamic partition attributes:

-- Modify the precreation cycle to 3 days, the 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 partition management
ALTER TABLE user_behavior DYNAMIC_PARTITION_POLICY(ENABLE = FALSE);

Extended configuration scenarios for dynamic partitions

Scenario: Extend data retention period due to compliance requirements

-- 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. In the following scenarios, you can manually adjust the settings:

Change 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 it to execute at 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 partition management task to immediately clean up expired partitions or precreate future partitions (such as for urgent storage recovery).

Designing a dynamic partition 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')
);

Verify the dynamic partitioning strategy

Step 1: Verify the 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

Verify 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 test data covering 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 within the initial partition boundary (before 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 verification

-- 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;

Data result:

+------------------+-------------+--------------+
| 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 the cleanup of 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 the partition management task

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 the 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 at the partition boundary (optional)

INSERT INTO user_behavior VALUES (999, '2025-11-13 20:00:00', 'future', 'test');
-- The following error will be generated: 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. The view reads data from memory and has better query performance.

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

References

MySQL mode:
Oracle mode:

OceanBase

Customer Stories

Documentation

Dynamic partitioning

Core values

Solution for dynamic partitioning

Features

Key scenarios

Modify dynamic partition attributes

Extended configuration scenarios for dynamic partitions

Partition management

Designing a dynamic partition table

Verify the dynamic partitioning strategy

Step 1: Verify the table configuration

Step 2: Insert test data

Step 3: Query verification

Step 4: Verify the cleanup of expired data (optional)

Step 5: Monitor the partition management task

Step 6: View the task execution history

Step 7: Verify the behavior at the partition boundary (optional)

Related views

Data dictionary views

Dynamic performance views

References

Dynamic partitioning

Core values

Solution for dynamic partitioning

Features

Key scenarios

Modify dynamic partition attributes

Extended configuration scenarios for dynamic partitions

Partition management

Designing a dynamic partition table

Verify the dynamic partitioning strategy

Step 1: Verify the table configuration

Step 2: Insert test data

Step 3: Query verification

Step 4: Verify the cleanup of expired data (optional)

Step 5: Monitor the partition management task

Step 6: View the task execution history

Step 7: Verify the behavior at the partition boundary (optional)

Related views

Data dictionary views

Dynamic performance views

References