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.

  • The change will be combined with a major software update, so it will anyhow be done during system downtime. I understand that you suggest using EXCHANGE PARTITION if there is downtime available? – paweloque Mar 11 '11 at 12:55
  • yes, it is the easiest. Test it. You might underestimate the time required. – ik_zelf Mar 11 '11 at 13:05

I agree with ik_zelf(+1). You will probably want to do an Exchange Partition, therefore you should read the SQL Language Reference information about it and the rules it must follow.

The purpose of DBMS_Redefinition is to make changes online rather than offline. You should read the Administrator's Guide to gain an understanding of the situations in which it can help you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.