How to Maintain Partitions in Oracle

The point of partitioning is that each piece of a large table can be handled on its own: split a partition that grew too large, empty one in a moment, drop last year's data without deleting a row, or swap in a freshly loaded table. ALTER TABLE has an operation for each, and each works on one partition rather than the whole table.

Code for This Guide

The main examples are in the examples/special-tables and examples/flashback-sampling folders of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

alter table table add partition ...
alter table table split partition p at (value) into (partition p1, partition p2)
alter table table merge partitions p1, p2 into partition p
alter table table truncate partition p
alter table table drop partition p
alter table table exchange partition p with table t
alter table table move partition p ...

Set Up a Partitioned Table

The maintenance example works on FLIGHT_LOG, a table with monthly interval partitions, created first:

Example:

create table flight_log (
  flight_id  number,
  flown_on   date,
  status     varchar2(10)
)
partition by range (flown_on) interval (numtoyminterval(1, 'MONTH'))
(partition p_before_2026 values less than (date '2026-01-01'));

insert into flight_log
select flight_id, cast(sys_extract_utc(scheduled_departure) as date), status from flights;
commit;
exec dbms_stats.gather_table_stats(user, 'FLIGHT_LOG')

select partition_name, interval, num_rows
from   user_tab_partitions
where  table_name = 'FLIGHT_LOG'
order  by partition_position;

Output:

Table FLIGHT_LOG created.

2,856 rows inserted.

Commit complete.

PL/SQL procedure successfully completed.

PARTITION_NAME    INTERVAL       NUM_ROWS
_________________ ___________ ___________
P_BEFORE_2026     NO                    5
SYS_P1638         YES                 981
SYS_P1639         YES                 888
SYS_P1640         YES                 981
SYS_P1641         YES                   1

Split, Truncate, and Drop

The first partition holds everything before 2026. It is split at 1 December 2025 into an old part and a December part, the old part is truncated, and then dropped.

Example:

alter table flight_log split partition p_before_2026 at (date '2025-12-01')
  into (partition p_before_dec_2025, partition p_dec_2025);
alter table flight_log truncate partition p_before_dec_2025;
alter table flight_log drop partition p_before_dec_2025;

select partition_name, partition_position
from   user_tab_partitions
where  table_name = 'FLIGHT_LOG'
order  by partition_position
fetch  first 3 rows only;

Output:

Table FLIGHT_LOG altered.

Table FLIGHT_LOG truncated.

Table FLIGHT_LOG altered.

PARTITION_NAME       PARTITION_POSITION
_________________ _____________________
P_DEC_2025                            1
SYS_P1638                             2
SYS_P1639                             3

The table now starts with the December 2025 partition, followed by the automatically created monthly ones. Drop it afterward with drop table flight_log purge.

Partition-Extended Names

Queries and DML can address a single partition by name, or through a key value with PARTITION FOR, which matters for system-named interval partitions.

Example:

create table flights_by_month
partition by range (dep_date) (
  partition p_2026_01 values less than (date '2026-02-01'),
  partition p_2026_02 values less than (date '2026-03-01'),
  partition p_2026_03 values less than (date '2026-04-01'),
  partition p_later   values less than (maxvalue)
)
as select flight_id, flight_no,
          cast(sys_extract_utc(scheduled_departure) as date) as dep_date
   from   flights;

select count(*) as feb_flights from flights_by_month partition (p_2026_02);

select count(*) as march_flights
from   flights_by_month partition for (date '2026-03-15');

Output:

Table FLIGHTS_BY_MONTH created.

   FEB_FLIGHTS
______________
           888

   MARCH_FLIGHTS
________________
             981

Things to Know

• Add UPDATE INDEXES to operations that move or remove rows, so global indexes stay usable.
• Dropping or truncating a partition is much faster than DELETE and generates almost no undo, but it cannot be rolled back.
• Interval partitions cannot be added with ADD PARTITION; they appear when rows arrive.

Related Guides

• How to Partition Tables by Range in Oracle
• How to Partition Tables by List in Oracle

Conclusion

ALTER TABLE splits, merges, truncates, drops, moves, and exchanges individual partitions, so large tables are maintained a piece at a time. Use partition names or PARTITION FOR to address one partition, and UPDATE INDEXES to keep global indexes valid.