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

Core values

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

Massive data growth: Continuous writes of logs, user behavior, and sensor data.
Complex resource management: Manual creation/deletion of partitions is time-consuming and error-prone.
Storage cost pressure: Automatic cleanup of historical data is needed to prevent storage inflation.

Solution for dynamic partitioning

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

Zero operational overhead: Precreation of partitions through scheduled tasks eliminates the need for manual intervention. (Dynamic partitioning creates partitions based on predefined rules through scheduled tasks; OceanBase Database 4.4.2 and 4.5.0 also support Interval partitioning, which automatically creates partitions during data writes.)
Smart cleanup: On-demand deletion of old partitions 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, 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 partitions for a certain period in the future, avoiding 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 data consistency across different time zones.
Allows configuration of the precision of bigint timestamps (such as BIGINT_PRECISION) to meet varying time precision requirements.

Key scenarios

Modifying dynamic partitioning properties

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

Here are examples of modifying dynamic partitioning properties:

-- Set the precreation period 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: Extending 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 using built-in scheduled tasks. Manual adjustments are supported 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 schedule the task to run 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 partitioning management task to immediately clean up expired partitions or precreate future partitions (e.g., for urgent storage recovery).

Designing dynamic partitioning tables

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 expansion, 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 are not immediately created after the table is created. They need to wait for a dynamic partition management schedule or be 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 is 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 a dynamic partition management schedule, 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 pre-created partitions 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 a dynamic partition management schedule, 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 range (< 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 partition expansion
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 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 output:

+------------------+-------------+--------------+
| 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 was successfully executed.

Step 7: Verify the behavior of inserting data at the boundary (optional)

Try to insert data beyond the pre-created range (assuming the current time is 2025-11-13 15:07:00, and insert data at 2025-11-13 20:00:00, with the pre-created upper limit at 2025-11-13 19: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

Dictionary views

You can query the DBA_OB_DYNAMIC_PARTITION_TABLES 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 data of dynamic partition tables in the current tenant.
The oceanbase.CDB_OB_DYNAMIC_PARTITION_TABLES view is visible only to the sys tenant and contains the data of dynamic partition tables in 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. This view provides better query performance because it reads data from memory.

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

References

MySQL mode:
Oracle mode:

Core values

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

Massive data growth: Continuous writes of logs, user behavior, and sensor data.
Complex resource management: Manual creation/deletion of partitions is time-consuming and error-prone.
Storage cost pressure: Automatic cleanup of historical data is needed to prevent storage inflation.

Solution for dynamic partitioning

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

Zero operational overhead: Precreation of partitions through scheduled tasks eliminates the need for manual intervention. (Dynamic partitioning creates partitions based on predefined rules through scheduled tasks; OceanBase Database 4.4.2 and 4.5.0 also support Interval partitioning, which automatically creates partitions during data writes.)
Smart cleanup: On-demand deletion of old partitions 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, 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 partitions for a certain period in the future, avoiding 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 data consistency across different time zones.
Allows configuration of the precision of bigint timestamps (such as BIGINT_PRECISION) to meet varying time precision requirements.

Key scenarios

Modifying dynamic partitioning properties

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

Here are examples of modifying dynamic partitioning properties:

-- Set the precreation period 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: Extending 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 using built-in scheduled tasks. Manual adjustments are supported 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 schedule the task to run 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 partitioning management task to immediately clean up expired partitions or precreate future partitions (e.g., for urgent storage recovery).

Designing dynamic partitioning tables

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 expansion, 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 range (< 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 partition expansion
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 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 output:

+------------------+-------------+--------------+
| 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 was successfully executed.

Step 7: Verify the behavior of inserting data at the boundary (optional)

Try to insert data beyond the pre-created range (assuming the current time is 2025-11-13 15:07:00, and insert data at 2025-11-13 20:00:00, with the pre-created upper limit at 2025-11-13 19: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

Dictionary views

You can query the DBA_OB_DYNAMIC_PARTITION_TABLES 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 data of dynamic partition tables in the current tenant.
The oceanbase.CDB_OB_DYNAMIC_PARTITION_TABLES view is visible only to the sys tenant and contains the data of dynamic partition tables in all tenants.

Dynamic performance views

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

References

MySQL mode:
Oracle mode:

OceanBase

Customer Stories

Documentation

Dynamic partitioning

Core values

Solution for dynamic partitioning

Features

Key scenarios

Modifying dynamic partitioning properties

Extended configuration scenarios for dynamic partitioning

Partition management

Designing dynamic partitioning tables

Verify the dynamic partitioning strategy

Step 1: Verify the table configuration

Step 2: Insert test data

Step 3: Query verification

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

Related views

Dictionary views

Dynamic performance views

References

Dynamic partitioning

Core values

Solution for dynamic partitioning

Features

Key scenarios

Modifying dynamic partitioning properties

Extended configuration scenarios for dynamic partitioning

Partition management

Designing dynamic partitioning tables

Verify the dynamic partitioning strategy

Step 1: Verify the table configuration

Step 2: Insert test data

Step 3: Query verification

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

Related views

Dictionary views

Dynamic performance views

References