Thanks for the question, stephen.
Asked: February 28, 2018 - 12:58 pm UTC
Last updated: February 28, 2018 - 2:38 pm UTC
Version: 5.1
Viewed 1000+ times
You Asked
Hello Tom
I need assistance.
Wee have not been monitoring database changes at work for our software. This has really become hectic and we have to find a way to track database changes.
Every developer has his own database instance, in this case, three developers. We manage changes by ensuring that each developer manually saves each change to the database I.e DDL statements to a folder. We then use flyaway to read the contents of this folder and update the staging and integration environments.
This is problematic since now developers are overwriting each others changes. This can be resolved by ultimately refreshing the databases every so and so. however this is not sustainable for the growing company.
I had a weird dream to do with database changes, and i woke up and researched on the DDL database level triggers. I can then use UTL-File to save changes to file.
Kindly advice how i can write the trigger to pick the author, date, statement and write that to a folder, depending on the type of object i.e table, type, sequence, with the object name as the file name. keeping in mind that ever y developer has his own machine and the database instances are hosted on a shared server.
I will really appreciate. Thanks in advance
and Chris said...
You can find an example of how to capture this information in this answer:
https://asktom.oracle.com/pls/apex/asktom.search?tag=ddl-trigger-200102 But
I feel like you're missing the point of Flyway.
Writing a DDL trigger to capture all changes won't magically fix everything. In development people often try things which are later scrapped. So you still need someone who understands the changes to identify what you need to apply to prod. And if there are conflicting changes, you don't know which to apply. The proposers of each change need to talk and agree how to resolve the mismatch.
A major reason for using a tool like Flyway is it versions your database schema. You run the migration to bring your database up-to-date. So the developers should use it this to apply other people's changes to their database. Bonus points if you can automate this process.