[14] Tracking Database schema changes (Oracle Only)

I needed to track Niku Schema changes and to have audit in place to track what got changed, when and by whom... I researched and got one method to track DB Schema changes on Production database, that I would like to share with you .. ( Remember this is all about Schema changes like SP, Triggers, Functions etc and not the data changes in Tables )

This method is about creating a 'Niku' Schema level trigger and that trigger inserting the change details in a Table. You can download code from here. If you have Niku user's password then it will do the job. I recommend, trying it on Dev/Test first and exclude events/records that you don't want to track in first IF condition of Trigger. It will give you Object name, date when changed/created, Terminal, OS User and other few details. It also stores the DDL of the object before it was changed ( in BeforeDDL field ) so you can track the changes for any object ( SP/Trigger/Function etc ). BeforeDDL field will show up the contents when you doubleclick it .. (in Toad)

Sometimes you may need to compare the contents of say a SP. I prefer using CompFold utility for content comparison that you can download from here . Save contents to file and then compare using CompFold ( or any other comparison tool of your choice )

Note : Disable this Trigger while doing upgrade or applying Service Pack (FP).