Dynamic partition management tasks

OceanBase Database introduces the system package DBMS_PARTITION for dynamic partition management, providing the subprogram MANAGE_DYNAMIC_PARTITION() to trigger dynamic partition management.

Scheduled dynamic partition management

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

• SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY schedules dynamic partition management with TIME_UNIT set to hour. It runs once at each hour and its schedule cannot be modified.
• SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY schedules dynamic partition management with TIME_UNIT set to day, week, month, or year. It runs once per day, defaulting to 00:00. Users can modify the schedule to avoid peak DDL business hours.

Query scheduled dynamic partition management tasks

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

Example:

• Specify JOB_NAME as SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY.

  SELECT JOB_NAME, JOB_ACTION, START_DATE, REPEAT_INTERVAL, ENABLED, NEXT_RUN_DATE, MAX_RUN_DURATION, COMMENTS
  FROM sys.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
  

• Specify JOB_NAME as SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY.

  SELECT JOB_NAME, JOB_ACTION, START_DATE, REPEAT_INTERVAL, ENABLED, NEXT_RUN_DATE, MAX_RUN_DURATION, COMMENTS
  FROM sys.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 for the current tenant in a user tenant.

Disable a scheduled partition management task

CALL DBMS_SCHEDULER.DISABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY');

CALL DBMS_SCHEDULER.DISABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY');

Enable a scheduled partition management task

CALL DBMS_SCHEDULER.ENABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_DAILY');

CALL DBMS_SCHEDULER.ENABLE('SCHEDULED_MANAGE_DYNAMIC_PARTITION_HOURLY');

Modify the daily schedule time of a scheduled partition management task

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

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

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

Manually trigger dynamic partition management

Call DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION() directly to trigger a one-time 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 larger value between the specified value and the precreate_time on the table is used for pre-creating partitions. Example:

  CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3DAY');
  

• When specifying time_unit, dynamic partition management is performed only when the table's time_unit matches the specified time_unit. Example:

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

Query historical execution records of dynamic partition management tasks

The view DBA_OB_TENANT_EVENT_HISTORY records the historical execution status of dynamic partition management tasks under the current tenant, including the table IDs for successful and failed executions:

SELECT * FROM sys.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 |
+------------------------------+----------------+--------------------------+-----------------------+--------------------------+----------------------+--------+-------+--------+-------+--------+-------+--------+-------+--------+------------+----------------+----------+-----------------------------------+-----------+----------+-----------+
| 15-APR-25 04.00.04.528017 PM | DBMS_PARTITION | MANAGE_DYNAMIC_PARTITION | SUCCESS_TABLE_ID_LIST | [500004]                 | FAILED_TABLE_ID_LIST | []     | NULL  | NULL   | NULL  | NULL   | NULL  | NULL   | NULL  | NULL   | NULL       | xxx.xx.xxx.xxx |     2882 | YB42AC1E87C3-000632CB999BC733-0-0 |   4505353 |        0 | NULL      |
+------------------------------+----------------+--------------------------+-----------------------+--------------------------+----------------------+--------+-------+--------+-------+--------+-------+--------+-------+--------+------------+----------------+----------+-----------------------------------+-----------+----------+-----------+
1 rows in set

References

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