0

During an application release it is common that changes are made to the associated database schema and potentially the data it contains. Should something go wrong with the application release, the changes made to the schema (and data) will need to be rolled back.

To throw a spanner in the works, there are multiple applications with their own schemas running on the same database instance.

What is the best way to create a back-up that would allow the application schema to be rolled back leaving all other schemas unaffected?

2

We have Flashback feature starting from Oracle 10g. But you only can flashback individual objects or whole database.

Now you have two options(IMHO):-

  • Data Pump(expdp/impdp):- You can export your schema before the testing and can import the dump file to restore schema to previous version.
  • You can write a script to flashback all the tables and dependent objects to the previous version.

    References:

  • Data Pump
  • FLASHBACK TABLE

    2

    Use the expdp to dump the schema. When needed you can later import the schema back with impdp.

    • Thanks for the answer, it's the mechanism that'll be used going forward, but @JSapkota pipped it with the detail about FLASHBACK TABLE. – Nick Holt Dec 12 '16 at 11:08

    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.