ORA-14652: partition rebuild name conflict fix

You get ORA-14652 when trying to rebuild a partition but the new partition name already exists in the same table. Real fix: pick a name that doesn't conflict or merge partitions first.

You're working on a large Oracle 12c or 19c OLTP table that's partitioned by month. One partition—say P_2023_01—has data you need to rebuild because it's grown fragmented. You run something like:

ALTER TABLE sales REBUILD PARTITION P_2023_01;

And Oracle throws ORA-14652: Partition name already exists in the table. Not helpful. You double-check: yes, the partition exists. Why can't it rebuild itself?

What's actually happening here

The root cause is a weird Oracle behavior in how REBUILD PARTITION works under the hood. When you rebuild a partition, Oracle doesn't just reorganize the segment in place. Instead, it creates a temporary partition with the same name internally during the rebuild process. If your table already has a partition with that exact name—which it does, since you're rebuilding it—Oracle sees the name conflict and gives you ORA-14652.

This happens most often when:

You have a table with ROW MOVEMENT disabled (the rebuild needs to move rows temporarily).
The partition is a referenced partition in a reference-partitioned child table.
You're running Oracle 12.1.0.2 or older (fixed in 12.2 and later patches).

But the real surprise: the error isn't about the partition name itself—it's about Oracle's internal temporary object creation. The fix doesn't require renaming your partition. You just need to work around the limitation.

The fix: two options

Skip the REBUILD PARTITION command. It's buggy here. Instead, use one of these reliable alternatives.

Option 1: Exchange and rebuild

This is my preferred fix. It's safe, works across Oracle versions, and doesn't touch the data.

Create an empty staging table with the same structure as your partitioned table:
```
CREATE TABLE sales_staging AS SELECT * FROM sales WHERE 1=0;
```
Exchange the partition with the staging table (this swaps data without moving it):
```
ALTER TABLE sales EXCHANGE PARTITION P_2023_01 WITH TABLE sales_staging;
```
Rebuild the partition's empty segment (now it's fresh):
```
ALTER TABLE sales MODIFY PARTITION P_2023_01 REBUILD;
```

Swap the data back:

ALTER TABLE sales EXCHANGE PARTITION P_2023_01 WITH TABLE sales_staging;

Drop the staging table:
```
DROP TABLE sales_staging;
```

The reason step 3 works: after exchanging, the partition has no rows, so Oracle doesn't need to create a temporary partition—it just rebuilds an empty segment. No name conflict.

Option 2: Move partition to a different tablespace

If you have multiple tablespaces, you can move the partition instead of rebuilding. This also defragments it.

Move the partition to a different tablespace:

ALTER TABLE sales MOVE PARTITION P_2023_01 TABLESPACE ts_sales_archive;

Move it back to the original tablespace:

ALTER TABLE sales MOVE PARTITION P_2023_01 TABLESPACE ts_sales;

This works because MOVE PARTITION doesn't create a temporary partition—it rebuilds the segment in place. Downside: it locks the partition for writes during the move. For OLTP systems, that's usually fine if you schedule it during maintenance.

What to check if it still fails

If both options still give you ORA-14652:

Check the partition name case. Oracle stores partition names in uppercase by default. If you created the partition with quoted identifiers like "P_2023_01", the name might be case-sensitive. Use SELECT partition_name FROM user_tab_partitions WHERE table_name = 'SALES' to see the exact case.
Look for orphaned partitions. Run SELECT partition_name, partition_position FROM user_tab_partitions WHERE table_name = 'SALES' ORDER BY partition_position. If you see duplicate names or gaps in positions, you have corrupted partition metadata. That's rare but fixable by dropping and recreating the problematic partition.
Check Oracle version and patches. Run SELECT * FROM v$version. If you're on 12.1.0.1 or 12.1.0.2, apply at least Patch 21580895 (fixed in 12.2.0.1). If you can't patch, stick with Option 1.
Verify row movement. Run SELECT row_movement FROM user_tables WHERE table_name = 'SALES'. If it's DISABLED, enable it temporarily: ALTER TABLE sales ENABLE ROW MOVEMENT. Then try the rebuild again. Disable it afterward if you don't need it.

One last thing: if you're rebuilding a partition because it's fragmented, check if you actually need to. In Oracle 12.2+, ALTER TABLE ... MOVE PARTITION online (with ONLINE keyword) might be safer. Rebuilding is for pre-12c habits—modern Oracle handles fragmentation better automatically.