[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).

[13] Clarity SQL Trace Tip

Clarity SQL Trace thing I often do and mostly to know which Query is performing badly and eating up most chunk of Execution time... For short SQL Trace files it's easy to locate but for some when file runs over 400 kb or so then Grrrrr..... Keep scrolling and scrolling..


Here is tip that can you use to avoid those 'scrolls' ... I use Textpad ( though you can use others ones too that support Regular Expression search ) for searching the SQL's that takes longer time. In Textpad check "Regular Expressions" and search for Time: [0-9][0-9][0-9][0-9] This will highlight the ones which takes over 1 sec, if you want to locate the ones which takes over 10 seconds then add another [0-9] in above expression.

[12] Quick DB Sync between Clarity environments (Oracle Only)

Many a times, I need to Take Production copy and replicate to some other environment to make it Production like. My pain that Export to be done strictly in off peak hours and it's Long process, takes hours and the Exp dmp file is too big to transfer and then import also eating up lots of disk space. Reason : Slices and Datamart tables are quite huge ones and makes this thing crazy.

In my case it was OK not to have Slices & Datamart tables Synch'd. So I thought if we can Exp & Imp all tables except Slices & datamart ones. I researched a bit and came up with a approach that would give me just what I wanted. In my case, Export was done in 20 minutes. File size was 2.2 GB ( when zipped it was 240 MB ). It was easy to transfer 240 mb file to different server and next 38 minutes to import into target database. CMN_CAPTIONS_NLS was largest table with 3.3 million records.

I have documented the complete process that I did. You can download from here. I am not database expert so not sure if there is any better way of doing it. May be if someone has better approach or suggesitons, please leave comment for this Post.

* I see another benefit with this approach. Apart from the backups that DBA has scheduled and goes into tape, I can schedule Exp job on another server which can keeps DMP of last 15 days. Typically backup of all those 15 days will not take more than 50 GB of space. And when needed you can get any of those 15 versions in very quick time ( as compared to Tape to be brought from different physical location, restored on separate server which typically takes 2 days)