I have an application which is running with an application schema on Oracle DB. The application needs to be upgraded to new version of Schema on Oracle DB with changes in Schema structure (Table Structure, Values, Entity Relations). Which tool/utility to use to reduce the downtime? Currently we are migrating the complete data using sql procedures and it is taking huge time (12 Hours). We want to reduce drastically the migration time at DB level.

1 Answer 1


Edition-Based Redefinition is perfect for this. You define schema changes in a new edition, along with procedures that are used to migrate the data between the base and new edition/schema. Switching between the two is seamless and can be done with zero downtime.

There's a good demo on the Oracle Base blog.

  • So basically i will have create crossedition triggers on both schemas. But Triggers will work only on new Data that is inserted, or data is updated or deleted after having these triggers in place. It wont work for the Data which is not modified at all. How does this data get migrated?
    – user37516
    Apr 24, 2014 at 15:36
  • Also what are the performance impacts of having triggers? will it result in slower processing of data from my application?
    – user37516
    Apr 24, 2014 at 15:37
  • If your app is hardcore OLTP you're going to need to do some benchmarking on a test environment. The data gets migrated using the triggers you create - you can do this [if the data is mutually exclusive - new columns, for example] before making the new edition live. (Covered in the blog post linked above, search for "DBMS_SQL").
    – Philᵀᴹ
    Apr 24, 2014 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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