SQL & PL/SQL

Hi all,
I’m looking for some guidance on a complex partitioning migration in Oracle 19c.
I’ve read a lot but I’m still quite unsure about the best approach, especially given the size of our production tables and the fact that our system runs 24/7.

📊 Current Setup

We have two very large tables in production:
||Table ||Rows (approx)||
|MSG_EXC_REC|2,291,902,813|
|MOVEMENT|621,941,623|

Both tables are range-partitioned by a date column (monthly) with an extra partition BEFORE_2010 for older data.

Example:

PARTITION BY RANGE (M_MIN_DATE)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION BEFORE_2010 VALUES LESS THAN (TIMESTAMP '2010-01-01 00:00:00')
)

💡 Proposed Solution

We are considering switching to composite partitioning:

• Partition by month (M_MIN_DATE)
• Subpartition by DOMAIN (Values can be NCTS, CCI, ICS, …)

We believe this will help Oracle perform both partition and subpartition pruning, significantly improving query performance.

🧠 My Understanding (and Confusion)

As far as I know, Oracle does not allow changing an existing partitioning scheme directly. So the migration process would likely involve:
1. Creating new tables with composite (range + list) partitioning
2. Writing procedures or scripts to transfer data in batches
3. Dropping or renaming old tables once migration is complete

However, I’m still uncertain about the practical approach for migrating such large datasets, especially given our system’s constraints.
My current thought is to schedule the migration during non-working hours (01:00–06:00) to minimize impact on users.
When we reach the final partition, would it be safer to bring the system down temporarily to complete the migration and ensure data consistency?

Key questions

1. Minimizing Impact
   1. What techniques or tools can help reduce downtime and performance impact during the migration?
   2. Is it better to transfer data directly or move/exchange partitions instead?Any techniques/tools to minimize impact? Should I transfer data or partitions ?
2. Safety Measures
   1. What safeguards (e.g., backups, validation checks, rollback plans) should be in place to ensure a safe migration?
3. Referential Integrity
   1. What’s the best way to preserve referential integrity throughout the migration process?
4. Constraint Handling
   1. If I disable constraints during the process, could I encounter “resource busy” errors when re-enabling them afterward?
   2. What’s the recommended way to handle such situations safely?

Any additional ideas or fallbacks — I’m a bit lost.
Thanks in advance