DBAtlas · Database operations atlas

Create interval partitioning and convert an existing range table to interval

OraclePartitioning

Command

-- New table: a partition is created automatically for each month
CREATE TABLE customer_app.measurements (
  ts        DATE   NOT NULL,
  sensor_id NUMBER,
  value     NUMBER)
PARTITION BY RANGE (ts) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p_first VALUES LESS THAN (DATE '2026-01-01') );

-- Convert an existing range table to interval (there must be no MAXVALUE partition):
ALTER TABLE customer_app.sale_part SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));
-- Back to range: ALTER TABLE customer_app.sale_part SET INTERVAL ();

-- Rename a system-named partition to a meaningful name:
ALTER TABLE customer_app.measurements RENAME PARTITION FOR (DATE '2026-03-15') TO p2026_03;

-- Check:
SELECT partition_name, partition_position, interval, high_value
FROM   dba_tab_partitions
WHERE  table_owner = 'CUSTOMER_APP' AND table_name = 'MEASUREMENTS'
ORDER  BY partition_position;

SELECT table_name, partitioning_type, interval
FROM   dba_part_tables
WHERE  owner = 'CUSTOMER_APP';

What it does

Interval partitioning creates a new partition automatically when the first row beyond the last bound arrives, eliminating ORA-14400 errors caused by forgetting next month's partition. SET INTERVAL converts an existing range table without moving data.

When to use it

For tables that grow by date, such as time series, logs and orders, and to reduce partition maintenance scripts.

Caution

Requires the Partitioning option (extra-cost EE). The partition key must be a single NUMBER/DATE/TIMESTAMP column; a MAXVALUE partition must be removed first. A bad or extreme date (e.g. year 9999) opens a far-off partition and distorts partition numbering; with narrow intervals you can approach the 1,048,575-partition limit (ORA-14300), so validate dates in the application. Automatic partitions go to the table's default tablespace (direct them with STORE IN).

Open in DBAtlas →

Related