Oracle Scratchpad

March 1, 2013

Source Control

Filed under: Advertisements,Oracle — Jonathan Lewis @ 6:37 pm GMT Mar 1,2013

You may recall that I spent some time with the developers at the redgate offices in Cambridge (UK) a little while ago, looking at their Source Control for Oracle package. The product is about to go live, with a launch date of 12th March.

Because of the help I’ve given them they’ve offered my readers the chance of winning one of two 5-user licences for the product – provided I devise a strategy for picking the recipients.

So, to make it easy, all you have to do is persuade me that you really need or really deserve a copy. Write up a short description in the comments of the worst problem you’ve had to face because you didn’t have a decent source control system, or the  best use you think you could make of a source control system for Oracle.

I’ll get the redgate developers to read what you wrote, and they will be the final judges of the two most interesting, or possibly most horrifying, or maybe even the most entertaining, cases.

Entries to be in by 23:59 GMT on 11th March.


The product has launched early !

You can find out more and see screenshots on this web page.

And there’s an online demo of the tool on March 14 at 16:00 GMT (17:00 CET / 11:00 EST / 10:00 CST / 08:00 PST), where you will be able to ask any questions. If you’re interesting in seeing it you’ll have to register at this “go to meeting” URL.

The closing date for entries is still 11th March, 23:59 GMT.


  1. In production, maximum time we don’t do complete package installation which means it only part of the object change however if you use older version of SVN then complete code need to deploy which makes things mad because the thing you dont want are going in.

    Second story is very difficult in one package having multiple branch then it was advisible to create separate projects but for developers its double work.

    Comment by Amit Verma — March 2, 2013 @ 3:59 pm GMT Mar 2,2013 | Reply

  2. I don’t really need the software but if you could ask them to stop tracking me with cookies everywhere I go I would appreciate it.

    Comment by john — March 4, 2013 @ 4:39 am GMT Mar 4,2013 | Reply

  3. We are SaaS company for financial transactions for small business, due to the SLAs with multiple clients, we maintain multiple copies of database procedure codebase in different set of schemas, we are having great deal of pain in understanding what version procedure/function/package….is in for particular customer environment, we use perforce for application code, and based bunch of tables and script names, we are able to do decent job in finding which object in what version and how to get a customer from version x to version y, and if a customer is in version d, what code that can be in common to version f, so that if version f ..i can go on and on…but we need a better source control for database objects, that is easy to adopt and have little to no overhead on database development group and be able to see delta between many different objects.

    If the RedGate product can answer standard version related questions and generate reports per customer environment that would be of great help.

    Thank you.

    Comment by Raj — March 4, 2013 @ 11:16 pm GMT Mar 4,2013 | Reply

  4. It would be very useful to have a tool specific to Oracle DB to do source control. Currently we are using audit create package, audit create procedure, etc. That way we store the different changes to source in dba_audit_trail. But it’s very painful to compare changes with previous version and it’s impossible to have branches.

    Comment by joaquingonzalez — March 5, 2013 @ 7:23 am GMT Mar 5,2013 | Reply

  5. Problems with database changes? Where to start? Databases must change in place using incremental changes (metamorphosis), whereas normal application software source code can just change and be compiled into a new, next version (evolution). You cannot lose the data within a production database when deploying a new version of the application software. I’ve blogged about this problem some time ago, and how it is a different kind of challenge to normal software source code versioning. Problems I’ve encountered?

    * Basic ability to produce upgrade SQL scripts containing the DDL to change the database structure. Having a uniform structure to these makes maintenance and debugging so much easier. Allowing any SQL by someone writing it themselves can make it difficult to work out precisely what kind of change is being done and why.

    * Sequencing of changes – which change should happen before another. You cannot index a table until it has been created. Similar issues over moving data between tables – you must make sure it is added to the new table before deleting or dropping the old table.

    * Atomic scripts – change scripts should do one and one thing only. The number of times I have had a change script do 10 or 20 different things and fail at step 13 for some reason or other. As DDL essentially auto-commits you cannot just rerun the script, because all of the first 12 steps will fail now for some reason. Or worse, do the same thing again such as inserting data into a reference table.

    * Another way around this is to write all database change scripts so that they detect whether each step has already been performed and skip that step if it has been run on this database. Another way of avoiding the rerun issue. This is termed “idempotent” I believe.

    * Not recording within the target database when each upgrade script was run so there is no audit trail. Knowing when each change to the database structure took place would be good. Equally, recording information about the type of change performed is useful – did it create a new table, modify an existing column, create a new index, update data in a table, insert data, delete data?

    * Inability to prove that creating a database directly at the final, current version results in the same structure as creating the database at an earlier version and then applying all upgrade scripts to it. Some kind of database structure comparison tool would be good. Not only to prove that the database structure is the same whichever route of upgrade scripts you take to get there, but also to show what is different between the two databases to help you identify the issue.

    * Lack of error checking or trapping with database change scripts. A blind assumption that each coded change to the database must work and that there will never be anything that could cause it to go wrong when run on a real, production database. Or the other extreme of trapping and capturing errors but then just saying “something went wrong” without providing any more details.

    * No pre-requisite checks being done by the upgrade script itself, that would otherwise avoid upgrade failures when it tries to make its changes to the database. With pre-upgrade checks not only are problems flagged earlier, but no database changes have been done by the upgrade script yet, so it can definitely be run again safely after the reported problems are addressed. See earlier point on being able to re-run large, complex upgrade scripts.

    I could go on, but I’ll stop there. I hope that is enough for you.


    Comment by John Brady — March 8, 2013 @ 3:40 pm GMT Mar 8,2013 | Reply

  6. I’ve just updated the posting – redgate have launched ahead of schedule. When was the last time you saw that happen ?!

    Some new links in the posting for information, links and a web demo with Q&A.

    Comment by Jonathan Lewis — March 8, 2013 @ 3:52 pm GMT Mar 8,2013 | Reply

  7. I’ve been a bit slow with the follow-up – I don’t know where the time went, but I seem to have had very little spare time lately for anything but the occasional drive-by blog post, or OTN comment – but James Murtagh, has picked the two winners and they are: joaquingonzalez and John Brady.

    I’ve emailed them privately with details on how to collect their prizes.

    Comment by Jonathan Lewis — April 29, 2013 @ 1:57 pm BST Apr 29,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply to joaquingonzalez Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by