I have an application running on Oracle 9i database and I was looking for something more practical to deploy data changes (not schema changes) from the development database to production. Currently I work as follows:

Set all the parameters needed on development database (this might take months), the user validates everything and then I have to apply everything again (which might take some more months). I can't just import the development database into production, because all the data from production will be lost. Is there something that can be done to avoid this "reworking"?

I tried Database Copy and Data Diff tools available on SQL Developer, but didn't work, since it has a lot of problems with dependencies when inserting new data on the tables..

The point is to keep the new/changed data made by the users in production while I work on the development database. Then import what I did without losing what the users did meanwhile.

Thanks in advance.

  • 2
    I'm not clear on the idea of importing data from the development database into production. This seems to break the point of having production and development. What kind of data is this? New properties or characteristics or user entered data?
    – kevinskio
    Dec 16, 2013 at 14:44
  • You're right! New properties and characs exploring the applications resources without having to execute SQL statements. Dec 16, 2013 at 15:51
  • The point is to keep the new/changed data made by the users in production while I work on the development database. Then import what I did without losing what the users did meanwhile. Hope it's clearer now... Dec 16, 2013 at 15:53

3 Answers 3


One way to do this is to ensure that all tables of properties, or metadata, have a date_created and date_last_modified date. Then you can filter where the date created is in a time period and export the changes as inserts or updates.

What I do is harder. All changes to development properties are saved as a script of inserts/updates and I use a project/bug tracker to keep track of all the collection of changes. I refresh development weekly so you see right away if you forgot something like a sequence or a grant. A typical change could involve data inserts, package updates, grants, sequences and triggers so you have to keep it organized.

Edit: Luke asks how do you keep changes as scripts? I do two things:

  • all properties and types are added by a package of procedures and functions. This checks that the input is valid, updates any logging/audit tables and validates the changes against any business logic you may have in the database. This is a lot of work to set up but allows you to have a consistent practice for adding new metadata. Each time you use a procedure or function you can save your call as an sql statement.
  • I use Toad and this has the ability to filter a table for almost any criteria and export the results as Inserts.
  • I've already thought about these filters, but it's not all the tables that contain dates.. And how do you save the changes as a script? Is it possible to do that using Oracle 9? I read that 11g can do that,but not sure about 9.. Thanks! Dec 16, 2013 at 17:45

You may want to record your changes in the development environment as a script or series of scripts such that they can be QA'd and deployed like any other piece of software.

Another solution, provided such changes are feasible, would be to segregate user data and data touched by developers. This would allow you to bulk load developer changes and not touch user data.

  • Thanks for your answer. How can I record the changes as a script? Does Oracle provide a tool for that? Dec 16, 2013 at 16:19
  • What are you using to make the changes if you aren't making structure changes? Are you authoring update/insert/delete statements yourself? If not, you could record them with a profiler. Check out this thread from StackOverflow on recording queries: stackoverflow.com/questions/148648/…
    – KeyboardG
    Dec 16, 2013 at 19:36

Son, you can apply the data change on production, export the result and then issue a rollback. After the user validates the result, execute the same script with COMMIT instead of ROLLBACK.

  • I can't do that, as these data changes are done using the application, not through SQL statements. Dec 16, 2013 at 11:35
  • 2
    How can the user see the data through the application if it hasn't been committed? Dec 16, 2013 at 13:52

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.