Dynamic partition management tasks

OceanBase Database introduces a new system package DBMS_PARTITION for dynamic partition management, which includes the MANAGE_DYNAMIC_PARTITION() subprogram for triggering dynamic partition management.

Schedule dynamic partition management tasks

Dynamic partition management tasks are scheduled using the PL system package DBMS_SCHEDULER. When a user tenant is created, two built-in scheduled dynamic partition management tasks, SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY and SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY, are provided. These tasks periodically call DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION().

• SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY schedules dynamic partition management for partitions whose TIME_UNIT is set to hour. It runs at the start of every hour, and the schedule time cannot be changed.
• SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY schedules dynamic partition management for partitions whose TIME_UNIT is set to day, week, month, or year. It runs once per day, by default at midnight (00:00). Users can modify the scheduled time to avoid peak DDL operation periods.

Query scheduled dynamic partition management tasks

Within a user tenant, you can query the scheduled dynamic partition management tasks by specifying JOB_NAME as either SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY or SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY in the oceanbase.DBA_SCHEDULER_JOBS view.

Here are some examples:

• Query the SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY task.

  SELECT JOB_NAME, JOB_ACTION, START_DATE, REPEAT_INTERVAL, ENABLED, NEXT_RUN_DATE, MAX_RUN_DURATION, COMMENTS 
  FROM oceanbase.DBA_SCHEDULER_JOBS
  WHERE JOB_NAME = 'SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY'\G
  

  The return result is as follows:

  *************************** 1. row ***************************
          JOB_NAME: SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY
        JOB_ACTION: DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION(null, 'DAY,WEEK,MONTH,YEAR')
        START_DATE: 2025-04-16 00:00:00.000000
   REPEAT_INTERVAL: FREQ=DAILY;INTERVAL=1
           ENABLED: 1
     NEXT_RUN_DATE: 2025-04-17 00:00:00.000000
  MAX_RUN_DURATION: 3600
          COMMENTS: used to perform manage dynamic partition periodically
  1 row in set
  

• Query the SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY task.

  SELECT JOB_NAME, JOB_ACTION, START_DATE, REPEAT_INTERVAL, ENABLED, NEXT_RUN_DATE, MAX_RUN_DURATION, COMMENTS
  FROM oceanbase.DBA_SCHEDULER_JOBS
  WHERE JOB_NAME = 'SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY'\G
  

  The return result is as follows:

  *************************** 1. row ***************************
          JOB_NAME: SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY
        JOB_ACTION: DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION(null, 'HOUR')
        START_DATE: 2025-04-15 15:00:00.000000
   REPEAT_INTERVAL: FREQ=HOURLY;INTERVAL=1
           ENABLED: 1
     NEXT_RUN_DATE: 2025-04-16 18:00:00.000000
  MAX_RUN_DURATION: 3600
          COMMENTS: used to perform manage dynamic partition periodically
  1 row in set
  

Modify scheduled dynamic partition management tasks

You can modify the scheduled dynamic partition management tasks in the user tenant for the current tenant.

Disable scheduled partition management tasks

CALL DBMS_SCHEDULER.DISABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY');

CALL DBMS_SCHEDULER.DISABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY');

Enable scheduled partition management tasks

CALL DBMS_SCHEDULER.ENABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY');

CALL DBMS_SCHEDULER.ENABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY');

Change the daily scheduling time for partition management tasks

You can modify only the daily scheduling time for the SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY scheduled task. For example, assume that the current time is 2025-03-11 10:00:00, and you want to change the scheduling time to 12:00 PM every day.

CALL DBMS_SCHEDULER.SET_ATTRIBUTE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY', 'START_DATE', '2025-03-11 12:00:00');

The next scheduled run will be at 2025-03-11 12:00:00, and it will continue to run at 12:00 every day thereafter.

Trigger dynamic partition management manually

You can directly call DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION() to trigger a dynamic partition management task:

CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION();

MANAGE_DYNAMIC_PARTITION has two parameters: precreate_time and time_unit. For more information, see MANAGE_DYNAMIC_PARTITION.

• When specifying precreate_time, the system will use the larger value between the specified value and the table's precreate_time to pre-create partitions. For example:

  CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3DAY');
  

• When specifying time_unit, only tables whose time_unit matches the specified value will undergo dynamic partition management. For example:

  CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION(NULL, 'day, week');
  

Query execution history of dynamic partition management tasks

The oceanbase.DBA_OB_TENANT_EVENT_HISTORY view records the execution history of dynamic partition management tasks for the current tenant, including both successful and failed executions with their table IDs:

SELECT * FROM oceanbase.DBA_OB_TENANT_EVENT_HISTORY
WHERE EVENT = 'MANAGE_DYNAMIC_PARTITION';

The return result is as follows:

+----------------------------+----------------+--------------------------+-----------------------+--------------------------+----------------------+--------+-------+--------+-------+--------+-------+--------+-------+--------+------------+----------------+----------+-----------------------------------+-----------+----------+-----------+
| TIMESTAMP                  | MODULE         | EVENT                    | NAME1                 | VALUE1                   | NAME2                | VALUE2 | NAME3 | VALUE3 | NAME4 | VALUE4 | NAME5 | VALUE5 | NAME6 | VALUE6 | EXTRA_INFO | SVR_IP         | SVR_PORT | TRACE_ID                          | COST_TIME | RET_CODE | ERROR_MSG |
+----------------------------+----------------+--------------------------+-----------------------+--------------------------+----------------------+--------+-------+--------+-------+--------+-------+--------+-------+--------+------------+----------------+----------+-----------------------------------+-----------+----------+-----------+
| 2025-04-15 16:00:02.292861 | DBMS_PARTITION | MANAGE_DYNAMIC_PARTITION | SUCCESS_TABLE_ID_LIST | [500003]                 | FAILED_TABLE_ID_LIST | []     | NULL  |        | NULL  |        | NULL  |        | NULL  |        | NULL       | xxx.xx.xxx.xxx |     2882 | YB42AC1E87C3-000632CB941BC66D-0-0 |   2277933 |        0 | NULL      |
+----------------------------+----------------+--------------------------+-----------------------+--------------------------+----------------------+--------+-------+--------+-------+--------+-------+--------+-------+--------+------------+----------------+----------+-----------------------------------+-----------+----------+-----------+
1 rows in set

References

• Overview of dynamic partitioning
• Create a dynamic partitioned table
• Query a dynamic partitioned table
• Modify a dynamic partitioned table