Managing Partitions in Oracle Database – A Complete Guide for DBAs
Partitioning in Oracle Database offers a powerful way to improve performance, manageability, and scalability for large datasets. By dividing a table or index into smaller, more manageable segments (partitions), you can optimize queries, streamline maintenance, and better control data storage.
This guide covers all the key operations you can perform on Oracle partitions with examples.
Common Partition Operations in Oracle
Oracle supports a variety of partition operations, including:
– ADD – Create a new partition
– DROP – Remove an existing partition
– TRUNCATE – Clear data in a partition
– COALESCE – Merge smaller subpartitions
– MODIFY – Alter a partition’s definition
– RENAME – Change a partition’s name
– MOVE – Relocate a partition to another tablespace
– EXCHANGE – Swap data between a partition and a table
– SPLIT – Divide a partition into multiple parts
– MERGE – Combine two or more partitions
– REBUILD – Recreate index segments for a partition
1. Creating a Partitioned Table
Creating partitions in Oracle is similar to creating a standard table but with a PARTITION BY clause. Before creating partitions:
– Decide which column(s) will be used for partitioning.
– Define value ranges or lists for each partition.
– Assign partitions to appropriate tablespaces.
CREATE TABLE sales_data
(
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date)
(
PARTITION q1_2025 VALUES LESS THAN (TO_DATE(’01-APR-2025′, ‘DD-MON-YYYY’)) TABLESPACE ts_q1,
PARTITION q2_2025 VALUES LESS THAN (TO_DATE(’01-JUL-2025′, ‘DD-MON-YYYY’)) TABLESPACE ts_q2,
PARTITION q3_2025 VALUES LESS THAN (TO_DATE(’01-OCT-2025′, ‘DD-MON-YYYY’)) TABLESPACE ts_q3,
PARTITION q4_2025 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_q4
);
2. Adding a Partition
Use ALTER TABLE … ADD PARTITION to add a new partition at the ‘high’ end unless the last partition uses MAXVALUE, in which case you’ll need SPLIT PARTITION.
ALTER TABLE sales_data
ADD PARTITION q1_2026
VALUES LESS THAN (TO_DATE(’01-APR-2026′, ‘DD-MON-YYYY’))
TABLESPACE ts_q1_2026;
3. Moving a Partition
Relocate a partition to balance I/O or optimize storage.
ALTER TABLE sales_data
MOVE PARTITION q4_2025 TABLESPACE ts_archive NOLOGGING;
4. Modifying a Partition
Change a partition’s values or subpartition template.
ALTER TABLE sales_data
MODIFY PARTITION q1_2025 ADD VALUES (‘NEW_VALUE’);
5. Renaming a Partition
Rename partitions for clarity or naming consistency.
ALTER TABLE sales_data
RENAME PARTITION q1_2025 TO q1_2025_updated;
6. Dropping a Partition
Remove a partition and optionally update global indexes.
ALTER TABLE sales_data
DROP PARTITION q3_2025 UPDATE GLOBAL INDEXES;
7. Truncating a Partition
Clear all rows from a partition.
ALTER TABLE sales_data
TRUNCATE PARTITION q2_2025 DROP STORAGE;
8. Coalescing Partitions
Mainly used for hash-partitioned tables.
ALTER TABLE sales_data COALESCE PARTITION q1_2025;
9. Splitting a Partition
Divide a partition into two smaller partitions.
ALTER TABLE sales_data
SPLIT PARTITION q4_2025 AT (TO_DATE(’01-DEC-2025′, ‘DD-MON-YYYY’))
INTO (
PARTITION dec_2025 TABLESPACE ts_dec,
PARTITION q1_2026 TABLESPACE ts_q1
) UPDATE GLOBAL INDEXES;
10. Exchanging a Partition with a Table
Swap data between a partition and a standalone table.
ALTER TABLE sales_data
EXCHANGE PARTITION q3_2025 WITH TABLE sales_q3_2025 VALIDATION;
11. Merging Partitions
Combine two or more partitions into one.
ALTER TABLE sales_data
MERGE PARTITIONS q1_2025, q2_2025 INTO PARTITION h1_2025;
12. Rebuilding Partition Indexes
Recreate partitioned index segments.
ALTER INDEX sales_idx
REBUILD PARTITION sales_idx_q1_2025 ONLINE;
13. Analyzing Partitions & Subpartitions
Gather statistics for better query optimization.
ANALYZE TABLE sales_data PARTITION (q1_2025) COMPUTE STATISTICS;
ANALYZE TABLE sales_data SUBPARTITION (q1_2025_jan) ESTIMATE STATISTICS;
Conclusion
Partition management in Oracle offers flexibility, scalability, and control over large datasets. From adding and moving partitions to splitting, merging, and exchanging them, these operations help DBAs fine-tune performance and maintenance without downtime.
