DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle In order to partition an existing non-partitioned table it is possible to use either the EXCHANGE PARTITION or DBMS_REDEFINITION. How to decide on which of those possibilities to choose for table partitioning. Does this depend on how many data reside in the table? Is one operation safer than the other?
in both ways you will end up using exchange partition and probably a series of split partitions. If your table does not have many dependencies like constraints and triggers, the route using redefinition can be easier because it buys you a lot of time. You prepare the new table as a copy and exchange the object id's on the end phase. If there are a lot of dependencies, you have to recreate them using different names.
The operation will take some time, depending on the size.
If you go for safe .... in both cases, the operations fail or succeed.
How is the load on the table? Does it receive a lot of updates/inserts/deletes during the migration? Do you have downtime available? I would go for simplicity if possible using downtime and not using redefinition.